三表动态行转列

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

 

--三表动态行转列
create table D
(
   [id] int primary key identity(1,1),
   [name] varchar(50)
)
insert into D([name]) values('销售部')
insert into D([name]) values('研发部')
insert into D([name]) values('行政部')
insert into D([name]) values('运营部')
go
create table G
(
   id int primary key identity(1,1),
   [name] varchar(50)
)
insert into G([name]) values('水笔')
insert into G([name]) values('鼠标')
insert into G([name]) values('键盘')
insert into G([name]) values('笔记本')
insert into G([name]) values('A4纸')
insert into G([name]) values('餐巾纸')
go
create table DG
(
   DID int,
   GID int,
   Money int
)
insert into DG(DID,GID,Money) values(1,1,50)
insert into DG(DID,GID,Money) values(1,2,30)
insert into DG(DID,GID,Money) values(1,3,30)
insert into DG(DID,GID,Money) values(2,1,60)
insert into DG(DID,GID,Money) values(2,2,80)
insert into DG(DID,GID,Money) values(2,3,120)
insert into DG(DID,GID,Money) values(3,4,30)
insert into DG(DID,GID,Money) values(3,5,40)
insert into DG(DID,GID,Money) values(3,6,20)
insert into DG(DID,GID,Money) values(4,1,10)
insert into DG(DID,GID,Money) values(4,2,90)
insert into DG(DID,GID,Money) values(4,3,70)
insert into DG(DID,GID,Money) values(1,4,50)
insert into DG(DID,GID,Money) values(2,5,30)
insert into DG(DID,GID,Money) values(3,6,80)
insert into DG(DID,GID,Money) values(4,1,30)
insert into DG(DID,GID,Money) values(1,1,30)
go
--单表的静态查询:
select did,[1],[2],[3],[4],[5],[6]
from DG
pivot
(sum(money) for gid in([1],[2],[3],[4],[5],[6]))t
/*
did         1           2           3           4           5           6
----------- ----------- ----------- ----------- ----------- ----------- -----------
1           80          30          30          50          NULL        NULL
2           60          80          120         NULL        30          NULL
3           NULL        NULL        NULL        30          40          100
4           40          90          70          NULL        NULL        NULL

(4 行受影响)
*/
--双表的静态查询,连接D表,改did为d表的name,注意name不能标到库中已有其他表的表名,否则会出错
select t.name as 部门,[1],[2],[3],[4],[5],[6]
from DG a inner join D t on a.DID=t.id
pivot
(sum(a.money) for a.gid in([1],[2],[3],[4],[5],[6]))t
/*
部门                                                 1           2           3           4           5           6
-------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -----------
销售部                                                80          30          30          50          NULL        NULL
研发部                                                60          80          120         NULL        30          NULL
行政部                                                NULL        NULL        NULL        30          40          100
运营部                                                40          90          70          NULL        NULL        NULL

(4 行受影响)
*/
--将动态查出G表的id值列表置于pivot,并将id作为查询列,以动态查出的G表name作为列别名进行查询.
declare @str1 nvarchar(1000),@str2 nvarchar(1000)
select @str1=ISNULL(@str1+',','')+'['+ltrim(id)+']',@str2=ISNULL(@str2+',','')+'['+LTRIM(id)+']['+name+']' from G
exec('select t.name as 部门,'+@str2+' from dg a inner join D t on a.DID=t.id
pivot (sum(a.money) for a.gid in('+@str1+'))t''+@str1+'))t')
'+@str1+'))tt')
/*
部门                                                 水笔          鼠标          键盘          笔记本         A4纸         餐巾纸
-------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -----------
销售部                                                80          30          30          50          NULL        NULL
研发部                                                60          80          120         NULL        30          NULL
行政部                                                NULL        NULL        NULL        30          40          100
运营部                                                40          90          70          NULL        NULL        NULL

(4 行受影响)

*/

go
drop table d,g,dg

另一个例子:

 

create table hospital(hospitalid int,hospitalname nvarchar(10))
insert into hospital select 1,'中心医院'
insert into hospital select 2,'郊区医院'
create table illType(illtypeID int,illTypeName nvarchar(10))
insert into illType select 1,'疾病A'
insert into illType select 2,'疾病B'
insert into illType select 3,'疾病C'
create table patient(id int,hospitalid int,illtypeID int)
insert into patient select 1,1,2
insert into patient select 2,1,3
insert into patient select 3,1,2
insert into patient select 4,2,1
insert into patient select 5,2,2
insert into patient select 6,2,2
go
declare @str1 nvarchar(4000),@str2 nvarchar(4000)  
select @str1=ISNULL(@str1+', ','')+'['+ltrim(hospitalid)+']',@str2=ISNULL(@str2+', ','')+'['+LTRIM(hospitalid)+']['+hospitalname+']' from hospital
exec('select illTypeName as 疾病种类,'+@str2+'
 from (select f.illTypeName,b.hospitalid from patient b inner join illType f on f.illtypeID=b.illtypeID)a
pivot
(count(hospitalid) for hospitalid in('+@str1+'))t')
/*
疾病种类       中心医院        郊区医院
---------- ----------- -----------
疾病A        0           1
疾病B        2           2
疾病C        1           0

(3 行受影响)

*/
go
drop table patient,illType,hospital