Tb_A ColA1是主键 , ColA2 是外键
ColA1 ColA2 ColA3 ColA4 ColA5
A001 B001 1 b1 c1
A002 B002 3 b3 c2
A003 B001 5 b5 c3Tb_B ColB1是外键
ColB1 ColB3 ColB4 ColB5
A001 AA 5 c1
A003 CC 6 c3
A002 BB 7 c2
A003 AA 8 c1
A002 CC 9 c1
A003 BB 10 c4
A001 BB 2 c1Tb_C ColB1是外键
ColC1 ColC2 ColC3
B001 aa bb
B002 cc dd
B003 ee ff 得到表的形式:
ColA1 colA3 ColA4 AA BB CC ColC2 ColC3
A001 1 b1 5 2 0 aa bb
A002 3 b3 0 7 9 cc dd
A003 5 b5 8 10 6 ee ff
ColA1 ColA2 ColA3 ColA4 ColA5
A001 B001 1 b1 c1
A002 B002 3 b3 c2
A003 B001 5 b5 c3Tb_B ColB1是外键
ColB1 ColB3 ColB4 ColB5
A001 AA 5 c1
A003 CC 6 c3
A002 BB 7 c2
A003 AA 8 c1
A002 CC 9 c1
A003 BB 10 c4
A001 BB 2 c1Tb_C ColB1是外键
ColC1 ColC2 ColC3
B001 aa bb
B002 cc dd
B003 ee ff 得到表的形式:
ColA1 colA3 ColA4 AA BB CC ColC2 ColC3
A001 1 b1 5 2 0 aa bb
A002 3 b3 0 7 9 cc dd
A003 5 b5 8 10 6 ee ff
问题是下面没有ColB1这个字段。
把ColB3 行 转换成列
ColB3 换成列后 对应的内容为 ColB4 种的值。
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([ColA1] nvarchar(4),[ColA2] nvarchar(4),[ColA3] int,[ColA4] nvarchar(2),[ColA5] nvarchar(2))
Insert #A
select N'A001',N'B001',1,N'b1',N'c1' union all
select N'A002',N'B002',3,N'b3',N'c2' union all
select N'A003',N'B001',5,N'b5',N'c3'
Go--> -->
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([ColB1] nvarchar(4),[ColB3] nvarchar(2),[ColB4] int,[ColB5] nvarchar(2))
Insert #B
select N'A001',N'AA',5,N'c1' union all
select N'A003',N'CC',6,N'c3' union all
select N'A002',N'BB',7,N'c2' union all
select N'A003',N'AA',8,N'c1' union all
select N'A002',N'CC',9,N'c1' union all
select N'A003',N'BB',10,N'c4' union all
select N'A001',N'BB',2,N'c1'
Go
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#C') is null
drop table #C
Go
Create table #C([ColC1] nvarchar(4),[ColC2] nvarchar(2),[ColC3] nvarchar(2))
Insert #C
select N'B001',N'aa',N'bb' union all
select N'B002',N'cc',N'dd' union all
select N'B003',N'ee',N'ff'
Go
Select
a.ColA1,a.colA3,a.ColA4,
sum(CASE WHEN b.ColB3='AA' THEN ColB4 ELSE 0 END) AS AA,
sum(CASE WHEN b.ColB3='BB' THEN ColB4 ELSE 0 END) AS BB,
sum(CASE WHEN b.ColB3='CC' THEN ColB4 ELSE 0 END) AS CC,
c.ColC2,c.ColC3
from #A AS a
LEFT JOIN #B AS b ON a.ColA1=b.ColB1
INNER JOIN #C AS c ON c.ColC1=a.ColA2
GROUP BY a.ColA1,a.colA3,a.ColA4,c.ColC2,c.ColC3
SELECT @s='Select a.ColA1,a.colA3,a.ColA4,'SELECT @s=@s+'sum(CASE WHEN b.ColB3='''+ColB3+''' then ColB4 else 0 end) as '+QUOTENAME(ColB3)+',' FROM #B GROUP BY ColB3
EXEC(@s+'c.ColC2,c.ColC3
from #A AS a
LEFT JOIN #B AS b ON a.ColA1=b.ColB1
INNER JOIN #C AS c ON c.ColC1=a.ColA2
GROUP BY a.ColA1,a.colA3,a.ColA4,c.ColC2,c.ColC3')