CODEWITHSUNDEEP
sql-serverpivotunpivot
Acunk
Acunk

Reputation: 13

Need help to PIVOT/UNPIVOT some data in SQL

I am having some difficulty trying to achieve some data in MSSQL. Here what I have:

SO_ID | SO_DATE | WAGE_A | WAGE_B | WAGE_B_DUR | 
1     | 12/12/06| 100    |   200  |    15      | 
2     | 10/10/06| 150    |   250  |    10      |

What I want :

SO_ID | SO_DATE | WAGE_TYPE | AMOUNT | DURATION |
1     | 12/12/06| WAGE_A    | 100    | NULL     |
1     | 12/12/06| WAGE_B    | 200    | 15       |
2     | 10/10/06| WAGE_B    | 250    | 10       |
2     | 10/10/06| WAGE_A    | 150    | NULL     |

I really appreciate it if you could give me some help or advice (including change the first table).

Upvotes: 1

Views: 45

Answers (2)

Eralper
Eralper

Reputation: 6622

You can use a simple SELECT with UNION ALL as follows

select
    SO_ID,
    SO_DATE,
    WAGE_TYPE = 'WAGE_A',
    AMOUNT = WAGE_A,
    DURATION = WAGE_B_DUR
from myData
union all
select
    SO_ID,
    SO_DATE,
    WAGE_TYPE = 'WAGE_B',
    AMOUNT = WAGE_B,
    DURATION = WAGE_B_DUR
from myData
order by SO_ID

Upvotes: 0

Ranjana Ghimire
Ranjana Ghimire

Reputation: 1815

Try as:

CREATE TABLE try_val(SO_ID int, SO_DATE int, WAGE_A int, WAGE_B int, WAGE_B_DUR int) 

insert into try_val values (1     , 12/12/06, 100    ,   200  ,    15      ) 
insert into try_val values (2     , 10/10/06, 150    ,   250  ,    10      )


select SO_ID,SO_DATE,'WAGE_A' AS WAGE_TYPE,WAGE_A AS AMOUNT,NULL AS DURATION from try_val 
UNION ALL
select SO_ID,SO_DATE,'WAGE_B' AS WAGE_TYPE,WAGE_A AS AMOUNT,WAGE_B_DUR  AS DURATION from try_val
ORDER BY SO_ID

Upvotes: 1

Related Questions