Brtdku

I need transpose multiple columns into one specific column SQL ( my real example has multiple columns and rows). Please see picture for details with what I have so far. I have also attached the full code for temp table that I created.

I have tried to solve the problem separately and created so far two queries:

• one is to stack all columns under one desired via union all ( not sure how to create dynamic table because my real example has a large data and doing manual insert wouldn't the best idea);


• pivot the data based on criteria and summing the totals

I don't know how to sum all my data under newly created 'columns' column from query # stacking and not sure if it is even possible.

----created new tables : test and plan test



create table test (

plancode int, 

amount int, 

category varchar (255),

ownership varchar (255),

status varchar (255))



select * from test



insert into test (

plancode, 

amount, 

category,

ownership,

status)

values (5,100,'parttime','simple', 'inprogress')



insert into test (

plancode, 

amount, 

category,

ownership,

status)

values (5,100,'fulltime','simple', 'inprogress')



insert into test (

plancode, 

amount, 

category,

ownership,

status)

values (10,100,'fulltime','simple', 'inprogress')



insert into test (

plancode, 

amount, 

category,

ownership,

status)

values (10,10,'partime','partial', 'complete')



insert into test (

plancode, 

amount, 

category,

ownership,

status)

values (15,100,'seasonal','full', 'complete')



insert into test (

plancode, 

amount, 

category,

ownership,

status)

values (15,200,'partime','simple','complete')



create table plantest (

plancode int,

planname varchar (255))



insert into plantest (

plancode,

planname)

values (5, 'plan A')



insert into plantest (

plancode,

planname)

values (10, 'plan B')



insert into plantest (

plancode,

planname)

values (15, 'plan C')



-----------------------------------------------------------------------------

Temp tables are created below 



select * from test



select * from plantest

________________________________________________________________

This #1 query is for union all. 

select plancode,

category as columns from test

union all

select plancode, ownership from test

union all

select plancode, status from test

This #2 query is for pivot all.

---pivoting on one of the columns, 'category' but I need to include all the other columns 'ownership' and 'status' right under the last value of 'category' column in this query

select * from (

select 

category,  ---only including one column.. that is why I thought if I stack all data under values here that it should work but right now it is not stacked. 

amount,

plancode 

from test) as tbl 

pivot (sum(amount) for plancode in ([5],[15],[10])) as pivottable