SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tbl (ID int, ServiceName varchar(100), ServiceCode varchar(100),
                 Age varchar(100), Gender varchar(100), [Number] int, 
                 TotalCost int, Location varchar(100));

insert into tbl values                 
(0, 'service1', '0000234', 'under 12', 'Male', 121, 1234, 'loc1');
1 rows affected

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));
select  
       ServiceName, 
       ServiceCode, 
       Age, 
       Gender, 
       'Number', 
       [Number], 
       location
from tbl


ServiceName
ServiceCode
Age
Gender
(No column name)
Number
location


service1
0000234
under 12
Male
Number
121
loc1


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

2 rows affected

select * from tbl2;


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

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