unpivot 2 columns into one column
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