Home > OS >  MS SQL Convert rows to columns
MS SQL Convert rows to columns

Time:05-11

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?

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

enter image description here

  • Related