Pivot on Multiple Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Pivot on Multiple Columns

Author

Topic

king_fisher
Starting Member

13 Posts

Posted - 2014-06-17 : 05:46:34

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 ,

vijay nelson

adsingh82
Starting Member

20 Posts

Posted - 2014-06-20 : 09:03:29

how did you get Plan1(1 to 12) and plan1 (12 to 24) from the table.

Regards,
Alwyn.M

king_fisher
Starting Member

13 Posts

Posted - 2014-06-26 : 00:18:46

i didn't get that,i am expecting result like that

vijay nelson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources