in MS SQL I have following table
I would like to convert it to :
I had a look at pivot table function, but could not get it work correctly. Any advice?
Advertisement
Answer
You coud try this:
x
With data (STOCKCODE, QTY, AGE) as (
select 'AIRFIL01', 3,1 union all
select 'AIRFIL01', 8,2 union all
select 'AIRFIL05', 4,1 union all
select 'AIRFIL05', 14,2 union all
select 'AIRPRE01', 4,1 union all
select 'AIRPRE01', 24,2 union all
select 'AIRSUS01', 1,2 union all
select 'ALARM01', 1,1 union all
select 'ALARM01', 6,2 union all
select 'ALARM01', 7,10 union all
select 'ALARM05', 2,1 union all
select 'ANTROL01', 5,2
)
SELECT * from (
Select STOCKCODE, QTY, CONCAT('Age_' , AGE) comment from data
)t
PIVOT
(
SUM(QTY)
FOR comment IN ( [Age_1],[Age_2],[Age_3],[Age_4],[Age_5],[Age_6],[Age_7],[Age_8],[Age_9],[Age_10])
) p