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 ) < 50
ORDER 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 JOIN
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 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 t
group by ADCD,WSCD) b
on a.adcd = b.adcd
left JOIN HSFX_RIVL c ON b.WSCD = c.BWSCD  COLLATE  Chinese_PRC_CS_AS

实现前：

实现后：