SQL技巧分享：一对多关系查询结果行转列汇总方法解析

sql中一对多关系的查询结果的多行转换成一行多列

1.创建测试表，

createtabletest_fee(useridnumber,feeidnumber,feenumber);

2.插入测试数据

insertintotest_feevalues(1,1001,80);

insertintotest_feevalues(1,1002,70);

insertintotest_feevalues(1,1003,90);

insertintotest_feevalues(1,1004,60);

insertintotest_feevalues(2,10)01,99);

insertintotest_feevalues(2,1002,66);

insertintotest_feevalues(2,1001,55);

3.查询表中所有记录，selectt.*,rowidfromtest_feet,

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

selectuserid,

sum(casewhenfeeid=1001thenfeeelse0end)asfee_1001,

sum（casewhenfeeid=1002thenfeeelse0end）asfee_1002，

sum(casewhenfeeid=1003thenfeeelse0end)asfee_1003,

sum(casewhenfeeid=1004thenfeeelse0end)asfee_1004

fromtest_feet

groupbyuserid

Sqlserver关于PIVOT行转列的问题