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