Click here to Skip to main content
15,880,543 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This is my sample Table structure

SQL
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 :

SQL
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:
SQL
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 8:28am
v4

1 solution

Try SELECT-GROUP BY-WITH CUBE SQL syntax:

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

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