Click here to Skip to main content
14,695,312 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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



i tred this :

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;


so, i got only plan wise ,i'm stuck in here.
guide me to get the Expected ResultSet

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



Thanks ,
Posted
Updated 18-Jun-14 9:28am
v4

1 solution

Try SELECT-GROUP BY-WITH CUBE SQL syntax:

http://careerride.com/SQL-server-CUBE-ROLLUP.aspx[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900