How do I pivot on multiple columns

Question

This is my sample Table structure

create table ##table1 (user_id int,plan_id int)
insert into ##table1 values(1,1)
insert into ##table1 values(2,1)
insert into ##table1 values(3,2)
insert into ##table1 values(4,2)
insert into ##table1 values(5,1)

select *From ##table1

create table ##payment (user_id int,dueno int,amount float)
insert into ##payment values(1,1,1000)
insert into ##payment values(2,1,1000)
insert into ##payment values(3,1,500)
insert into ##payment values(3,2,500)
insert into ##payment values(4,3,1500)
insert into ##payment values(5,2,100)
insert into ##payment values(5,1,100)

select *from ##payment

This is what I have so far:

with help as
(
    select a.user_id,a.plan_id,b.amount,b.dueno 
    from ##table1 as a 
         inner join ##payment as b on a.user_id=b.user_id
)
select *from  help pivot (sum(amount) for plan_id in ([1],[2],[3]))as pvt;

This is where I'm stuck.

Expected result:

user_id  plan1(1to12) plan1(12to24) plan2(1to12) plan2(12to24) plan3(1to12) plan4(12to24)
1         1000         null         null           null         null         null
2         1000         null         null           null         null         null 
3         null         null         1000           null         null         null
4         null         null         1500           null         null         null
5         200          200          null           null         null         null

What are 1,12,24 in the column headers plan1(1to12), plan1(12to24) etc. Are they plan ids? — Kiran Hegde, Jun 17 '14 at 13:00
The data and the expected result does not match where does the 200s for user_id 5 came from? And the 1000 for the used_id 3? — Serpiton, Jun 17 '14 at 14:13
please run the above query what i have mentioned and have a look on insert Query .i have insert 1000 rs for userid 3 and 200 rs for user_id 5. — King_Fisher, Jun 18 '14 at 04:30

score 0 · Answer 1 · answered Jun 17 '14 at 13:58

0

Use SUM + CASE instead of pivot

...
SELECT user_id
      ,SUM(CASE WHEN plan_id = 1 AND dueno BETWEEN 1 AND 12 THEN amount ELSE NULL END) AS [plan1(1to12)]
      ,SUM(CASE WHEN plan_id = 1 AND dueno BETWEEN 12 AND 24 THEN amount ELSE NULL END) AS [plan1(12to24)]
      ,SUM(CASE WHEN plan_id = 2 AND dueno BETWEEN 1 AND 12 THEN amount ELSE NULL END) AS [plan2(1to12)]
      ,SUM(CASE WHEN plan_id = 2 AND dueno BETWEEN 12 AND 24 THEN amount ELSE NULL END) AS [plan2(12to24)]
      ,SUM(CASE WHEN plan_id = 3 AND dueno BETWEEN 1 AND 12 THEN amount ELSE NULL END) AS [plan3(1to12)]
      ,SUM(CASE WHEN plan_id = 4 AND dueno BETWEEN 12 AND 24 THEN amount ELSE NULL END) AS [plan4(12to24)]
FROM help

(Your didn't specify if the dueno ranges are inclusive. You need to adjust the ranges accordingly. plan4(12to24) seems like a typo)

answered Jun 17 '14 at 13:58

adrianm

14,468
5
55
102

thank you, this is my sample Structure. i have more than 8 plan's and 120 due's .it gonna be take long time to write query and better if its dynamic.is there anyway to use Pivot – King_Fisher Jun 18 '14 at 04:24
My solution is easy to understand and maintain. If it is faster for you to research a pivot solution you need to improve your typing speed. :-) – adrianm Jun 18 '14 at 06:32
i agree,really its a good Solution but,its a Report for multiple company,each company have different plan's and dueno's.if its static i have to change for every time. :) – King_Fisher Jun 18 '14 at 09:06

score 0 · Answer 2 · answered Jun 17 '14 at 14:23

May be you can try this.

with help as
(
    select a.user_id,'plan' + CAST(a.plan_id as nvarchar) + '(1 to 12)' as planName , a.plan_id,b.amount,b.dueno 
    from (select distinct plan_id from ##table1) as ids
    left join ##table1 as a on ids.plan_id = a.plan_id
         inner join ##payment as b on a.user_id=b.user_id and b.dueno >= 1 and b.dueno <=12     
    union

    select a.user_id,'plan' + CAST(a.plan_id as nvarchar) + '(13 to 24)' as planName,a.plan_id,b.amount,b.dueno 
    from (select distinct plan_id from ##table1) as ids
    left join ##table1 as a on ids.plan_id = a.plan_id
         left join ##payment as b on a.user_id=b.user_id and b.dueno >= 13 and b.dueno <=24

)
select user_id
,sum([plan1(1 to 12)]) AS [plan1(1 to 12)] 
,sum([plan1(13 to 24)]) AS [plan1(13 to 24)] 
,sum([plan2(1 to 12)]) AS [plan2(13 to 24)] 
,sum([plan3(1 to 12)]) AS [plan3(1 to 12)] 
,sum([plan3(13 to 24)]) AS [plan3(13 to 24)] 
from  help pivot (sum(amount) for planName 
in ([plan1(1 to 12)],[plan1(13 to 24)]
    ,[plan2(1 to 12)],[plan2(13 to 24)]
    ,[plan3(1 to 12)],[plan3(13 to 24)]
    ))as pvt
    group by user_id;

thanks you for your reply ,but each plan have the different Dueno's better if its dynamic sir. — King_Fisher, Jun 18 '14 at 04:22

How do I pivot on multiple columns

2 Answers2