MS SQL Convert rows to columns with Pivot

Question

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?

"I had a look at pivot table function, but could not get it work correctly" And what was that attempt? You forgot to include it. Why didn't the attempt work? Also, don't post images of data. Provide sample data as text; preferably as DDL and DML statements but at least as well formatted tabular text or a markdown table. — Thom A
– Thom A ♦, Commented May 10, 2022 at 8:51

W_O_L_F · Accepted Answer · 2022-05-10 10:35:03Z

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

Collectives™ on Stack Overflow

MS SQL Convert rows to columns with Pivot

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related