MS SQL Convert rows to columns with Pivot

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?

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