Skip to content
Advertisement

MS SQL Convert rows to columns with Pivot

in MS SQL I have following table

source table

I would like to convert it to :

Destination table

I had a look at pivot table function, but could not get it work correctly. Any advice?

Advertisement

Answer

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

enter image description here

9 People found this is helpful
Advertisement