1

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?

3

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<>fiddle here

0

Without UNION:

insert into tbl2 (ServiceName, ServiceCode, Age, Gender, Measure, Value, Location)
    select ServiceName, ServiceCode, Age, Gender, 
    case x when 1 then 'Number' else 'TotalCost' end, 
    case x when 1 then [Number] else [TotalCost] end, location
    from tbl,(values(1),(2)) X(x);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.