sqlserver多行多列转一行多列

https://zhidao.baidu.com/question/871893841426870532.html

1.学习记录，简单可用很不错！

1、创建测试表，

create table test_fee(userid number, feeid number, fee number);

2、插入测试数据

insert into test_fee values(1,1001,80);

insert into test_fee values(1,1002,70);

insert into test_fee values(1,1003,90);

insert into test_fee values(1,1004,60);

insert into test_fee values(2,1001,99);

insert into test_fee values(2,1002,66);

insert into test_fee values(2,1001,55);

3、查询表中所有记录，select t.*, rowid from test_fee t，

4、编写sql，按userid汇总，根据不同的feeid，进行行转列汇总,

select userid,

sum(case when feeid = 1001 then fee else 0 end) as fee_1001, sum(case when feeid = 1002 then fee else 0 end) as fee_1002, sum(case when feeid = 1003 then fee else 0 end) as fee_1003, sum(case when feeid = 1004 then fee else 0 end) as fee_1004

from test_fee t

group by userid

2.改写自己的sql进行应用记录 SELECT * FROM IA_A_SWLLRKTB where CAST ( CXQ AS int ) < 50ORDER BY ADCD,CAST ( CXQ AS int ) ASC--多行多列转一行多列 SELECT DISTINCT a.CZZF,a.CZZW,a.ADCD,A.HECD,c.RVCD, WSCD,Q_5,Q_10, Q_20, Q_50,Q_100 from HSFX_CZZ a left JOINSELECT DISTINCT a.CZZF,a.CZZW,a.ADCD,A.HECD,c.RVCD, WSCD,Q_5,Q_10, Q_20, Q_50,Q_100 from HSFX_CZZ a left JOIN(select ADCD, WSCD, sum(case when CXQ = 5 then Q else 0 end) as Q_5, sum(case when CXQ = 10 then Q else 0 end) as Q_10, sum(case when CXQ = 20 then Q else 0 end) as Q_20, sum(case when CXQ = 50 then Q else 0 end) as Q_50, sum(case when CXQ = 100 then Q else 0 end) as Q_100 from IA_A_SWLLRKTB tgroup by ADCD,WSCD) bon a.adcd = b.adcdleft JOIN HSFX_RIVL c ON b.WSCD = c.BWSCD COLLATE Chinese_PRC_CS_AS

实现前：

实现后：