在Sql Server中的多个列上进行透视 [英] Pivot on multiple Columns in Sql Server

问题描述

这是我的样本表结构

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：

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 ,

推荐答案

尝试SELECT-GROUP BY-WITH CUBE SQL语法：



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

Try SELECT-GROUP BY-WITH CUBE SQL syntax:

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

这篇关于在Sql Server中的多个列上进行透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！