sql server - Transpose and insert subquery into outer query (SQL, select) -

through subquery i'm trying insert values , categories columns of outer query.

this makes more complex simple tranpose. column header of 'origin' column needs inserted value of column , value of 'origin' column goes yet column such:

cywp    name  qty       pr2 201607  m3    618400    0 201607  r1    329400    0 201607  m1    100056    0 201607  m2    20800     0 201607  r2    878921    113884 

becomes:

cywp    name  qty 201607  m3    618400 201607  r1    329400 201607  m1    100056 201607  m2    20800 201607  r2    878921 201607  pr2   113884 

the code have far;

select  p.cuttingyearweekplanned, ps.name,      sum(case    when p.productionstateidto = 2 or p.productionstateidto =101                 (  case when p.quantityplannedadjusted > 0                         p.quantityplannedadjusted                         else p.quantityplanned end)                 else (  case when p.quantityplannedadjusted > 0                          ceiling(convert(decimal(10,1),p.quantityplannedadjusted)/100)*100                         else ceiling(convert(decimal(10,1),p.quantityplanned)/100)*100 end)                 end) qtyplanned,     sum(case    when p.productionstateidto = 101 , b.quantityprer2orplastic > 0                 b.quantityprer2orplastic                 else 0                 end) pr2  productionorder p  inner join productionstate ps on p.productionstateidto = ps.productionstateid inner join batch b  on p.batchid = b.batchid  group p.cuttingyearweekplanned, ps.name 

the difficulty values in column 'qty' come different table values of column 'pr2'. 'group by' clause in outer query starts messing sums every attempt make transpose , insert results of pr2 column.

i think union all. have no idea relationship query has question -- question mentions 1 table, query has many.

here idea:

select cywp, name, qty t union select cywp, 'pr2', qty t pr2 <> 0;