--三表动态行转列

今日论坛上碰到一个帖子,要求对三表进行连接后动态行转列,先前只做过两表的,研究了一番,终于做成,列于后,并添加了注释.

 

view plain

1. --三表动态行转列  
2. create table D  
3. (  
4.    [id] int primary key identity(1,1),  
5.    [name] varchar(50)  
6. )  
7. insert into D([name]) values('销售部')  
8. insert into D([name]) values('研发部')  
9. insert into D([name]) values('行政部')  
10. insert into D([name]) values('运营部')  
11. go  
12. create table G  
13. (  
14.    id int primary key identity(1,1),  
15.    [name] varchar(50)  
16. )  
17. insert into G([name]) values('水笔')  
18. insert into G([name]) values('鼠标')  
19. insert into G([name]) values('键盘')  
20. insert into G([name]) values('笔记本')  
21. insert into G([name]) values('A4纸')  
22. insert into G([name]) values('餐巾纸')  
23. go  
24. create table DG  
25. (  
26.    DID int,  
27.    GID int,  
28.    Money int  
29. )  
30. insert into DG(DID,GID,Money) values(1,1,50)  
31. insert into DG(DID,GID,Money) values(1,2,30)  
32. insert into DG(DID,GID,Money) values(1,3,30)  
33. insert into DG(DID,GID,Money) values(2,1,60)  
34. insert into DG(DID,GID,Money) values(2,2,80)  
35. insert into DG(DID,GID,Money) values(2,3,120)  
36. insert into DG(DID,GID,Money) values(3,4,30)  
37. insert into DG(DID,GID,Money) values(3,5,40)  
38. insert into DG(DID,GID,Money) values(3,6,20)  
39. insert into DG(DID,GID,Money) values(4,1,10)  
40. insert into DG(DID,GID,Money) values(4,2,90)  
41. insert into DG(DID,GID,Money) values(4,3,70)  
42. insert into DG(DID,GID,Money) values(1,4,50)  
43. insert into DG(DID,GID,Money) values(2,5,30)  
44. insert into DG(DID,GID,Money) values(3,6,80)  
45. insert into DG(DID,GID,Money) values(4,1,30)  
46. insert into DG(DID,GID,Money) values(1,1,30)  
47. go  
48. --单表的静态查询:  
49. select did,[1],[2],[3],[4],[5],[6]  
50. from DG  
51. pivot  
52. (sum(money) for gid in([1],[2],[3],[4],[5],[6]))t  
53. /*  
54. did         1           2           3           4           5           6  
55. ----------- ----------- ----------- ----------- ----------- ----------- -----------  
56. 1           80          30          30          50          NULL        NULL  
57. 2           60          80          120         NULL        30          NULL  
58. 3           NULL        NULL        NULL        30          40          100  
59. 4           40          90          70          NULL        NULL        NULL  
60.   
61. (4 行受影响)  
62. */  
63. --双表的静态查询,连接D表,改did为d表的name,注意name不能标到库中已有其他表的表名,否则会出错  
64. select t.name as 部门,[1],[2],[3],[4],[5],[6]  
65. from DG a inner join D t on a.DID=t.id  
66. pivot  
67. (sum(a.money) for a.gid in([1],[2],[3],[4],[5],[6]))t  
68. /*  
69. 部门                                                 1           2           3           4           5           6  
70. -------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -----------  
71. 销售部                                                80          30          30          50          NULL        NULL  
72. 研发部                                                60          80          120         NULL        30          NULL  
73. 行政部                                                NULL        NULL        NULL        30          40          100  
74. 运营部                                                40          90          70          NULL        NULL        NULL  
75.   
76. (4 行受影响)  
77. */  
78. --将动态查出G表的id值列表置于pivot,并将id作为查询列,以动态查出的G表name作为列别名进行查询.  
79. declare @str1 nvarchar(1000),@str2 nvarchar(1000)  
80. select @str1=ISNULL(@str1+',','')+'['+ltrim(id)+']',@str2=ISNULL(@str2+',','')+'['+LTRIM(id)+']['+name+']' from G  
81. exec('select t.name as 部门,'+@str2+' from dg a inner join D t on a.DID=t.id  
82. pivot (sum(a.money) for a.gid in('+@str1+'))t''+@str1+'))t')  
83. '+@str1+'))tt')  
84. /*  
85. 部门                                                 水笔          鼠标          键盘          笔记本         A4纸         餐巾纸  
86. -------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -----------  
87. 销售部                                                80          30          30          50          NULL        NULL  
88. 研发部                                                60          80          120         NULL        30          NULL  
89. 行政部                                                NULL        NULL        NULL        30          40          100  
90. 运营部                                                40          90          70          NULL        NULL        NULL  
91.   
92. (4 行受影响)  
93.   
94. */  
95.   
96. go  
97. drop table d,g,dg  

另一个例子:

 

view plain

1. create table hospital(hospitalid int,hospitalname nvarchar(10))  
2. insert into hospital select 1,'中心医院'  
3. insert into hospital select 2,'郊区医院'  
4. create table illType(illtypeID int,illTypeName nvarchar(10))  
5. insert into illType select 1,'疾病A'  
6. insert into illType select 2,'疾病B'  
7. insert into illType select 3,'疾病C'  
8. create table patient(id int,hospitalid int,illtypeID int)  
9. insert into patient select 1,1,2  
10. insert into patient select 2,1,3  
11. insert into patient select 3,1,2  
12. insert into patient select 4,2,1  
13. insert into patient select 5,2,2  
14. insert into patient select 6,2,2  
15. go  
16. declare @str1 nvarchar(4000),@str2 nvarchar(4000)    
17. select @str1=ISNULL(@str1+', ','')+'['+ltrim(hospitalid)+']',@str2=ISNULL(@str2+', ','')+'['+LTRIM(hospitalid)+']['+hospitalname+']' from hospital  
18. exec('select illTypeName as 疾病种类,'+@str2+'  
19.  from (select f.illTypeName,b.hospitalid from patient b inner join illType f on f.illtypeID=b.illtypeID)a  
20. pivot  
21. (count(hospitalid) for hospitalid in('+@str1+'))t')  
22. /*  
23. 疾病种类       中心医院        郊区医院  
24. ---------- ----------- -----------  
25. 疾病A        0           1  
26. 疾病B        2           2  
27. 疾病C        1           0  
28.   
29. (3 行受影响)  
30.   
31. */  
32. go  
33. drop table patient,illType,hospital