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