unpivot 2 columns into one column I have tables like this:

ID|ServiceName|ServiceCode|Age      |Gender|Number|TotalCost|Location
0 | service1  |  0000234  |under 12 | Male | 121  |1234     |loc1


I want to change the table like this:

ID|ServiceName|ServiceCode|Age      |Gender|Measure    |Value |Location
0 | service1  |  0000234  |under 12 | Male | Number    |121     |loc1
1 | service1  |  0000234  |under 12 | Male | TotalCost |1234    |loc1


How can I do that?
You can use two SELECTs, one for each Measure.




create table tbl2 (ID int identity, ServiceName varchar(100), ServiceCode varchar(100),
                 Age varchar(100), Gender varchar(100), 
                 Measure varchar(100), [Value] int, Location varchar(100));






insert into tbl2 (ServiceName, ServiceCode, Age, Gender, Measure, Value, Location)
select ServiceName, ServiceCode, Age, Gender, 'Number', [Number], location
from tbl
union
select ServiceName, ServiceCode, Age, Gender, 'TotalCost', [TotalCost], location
from tbl






select * from tbl2;
GO



ID | ServiceName | ServiceCode | Age      | Gender | Measure   | Value | Location
-: | :---------- | :---------- | :------- | :----- | :-------- | ----: | :-------
 1 | service1    | 0000234     | under 12 | Male   | Number    |   121 | loc1    
 2 | service1    | 0000234     | under 12 | Male   | TotalCost |  1234 | loc1    



db&lt;>fiddle here