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?
CodePudding user response:
You coud try this:
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