--------例子------
CREATE TABLE tb(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)
INSERT tb SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red', -23
UNION ALL SELECT 'bb','Cup' ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red', -90--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s='SELECT Groups'
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+','+QUOTENAME(Item)
+N'=SUM(CASE Item WHEN '+QUOTENAME(Item,N'''')
+N' THEN Quantity END)'
FROM tb
GROUP BY Item
--生成列记录水平显示的处理代码拼接(处理Color列)
SELECT @s=@s
+','+QUOTENAME(Color)
+N'=SUM(CASE Color WHEN '+QUOTENAME(Color,N'''')
+N' THEN Quantity END)'
FROM tb
GROUP BY Color
--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+N'
FROM tb
GROUP BY Groups')
/*--结果
Groups Chair Cup Table Blue Green Red
------------ --------------- -------------- ------------- ------------ -------------- -----------
aa 11 NULL 124 225 NULL -90
bb NULL -23 -23 NULL -23 -23
--*/
CREATE TABLE tb(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)
INSERT tb SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red', -23
UNION ALL SELECT 'bb','Cup' ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red', -90--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s='SELECT Groups'
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+','+QUOTENAME(Item)
+N'=SUM(CASE Item WHEN '+QUOTENAME(Item,N'''')
+N' THEN Quantity END)'
FROM tb
GROUP BY Item
--生成列记录水平显示的处理代码拼接(处理Color列)
SELECT @s=@s
+','+QUOTENAME(Color)
+N'=SUM(CASE Color WHEN '+QUOTENAME(Color,N'''')
+N' THEN Quantity END)'
FROM tb
GROUP BY Color
--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+N'
FROM tb
GROUP BY Groups')
/*--结果
Groups Chair Cup Table Blue Green Red
------------ --------------- -------------- ------------- ------------ -------------- -----------
aa 11 NULL 124 225 NULL -90
bb NULL -23 -23 NULL -23 -23
--*/
INSERT tb SELECT '1','d',100 UNION ALL SELECT '2','d',200
UNION ALL SELECT '3','d',300 UNION ALL SELECT '1','e',1000
UNION ALL SELECT '2','e',2000 UNION ALL SELECT '3','e',3000
UNION ALL SELECT '1','f',10 UNION ALL SELECT '2','f',20 UNION ALL SELECT '3','f',30--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s='SELECT id'
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+','+QUOTENAME(cc)
+N'=SUM(CASE cc WHEN '+QUOTENAME(cc,N'''')
+N' THEN Value END)'
FROM tb
GROUP BY cc--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+N'
FROM tb
GROUP BY id')drop table tb
(
ID INT,
CC VARCHAR(5),
VALUE INT
)
INSERT INTO A
SELECT 1,'D',100 UNION ALL
SELECT 2,'D',200 UNION ALL
SELECT 3,'D',300 UNION ALL
SELECT 1,'E',1000 UNION ALL
SELECT 2,'E',2000 UNION ALL
SELECT 3,'E',3000 UNION ALL
SELECT 1,'F',10 UNION ALL
SELECT 2,'F',20 UNION ALL
SELECT 3,'F',30
GO
DECLARE @S1 VARCHAR(100)
DECLARE @S VARCHAR(1000)
SET @S1='SELECT ID,'
SET @S=' FROM (SELECT AA.ID,'
SELECT @S1=@S1+'MAX('+CC+') '+CC+',',@S=@S+'CASE CC WHEN '''+CC+''' THEN (SELECT [VALUE] FROM A WHERE CC='''+CC+''' AND ID=AA.ID) ELSE 0 END AS '+CC+',' FROM
(SELECT DISTINCT CC FROM A) B
SET @S=STUFF(@S1,LEN(@S1),1,'')+STUFF(@S,LEN(@S),1,'')+' FROM A AA) B GROUP BY B.ID'
EXEC(@S)
--结果
ID D E F
----------- ----------- ----------- -----------
1 100 1000 10
2 200 2000 20
3 300 3000 30(3 行受影响)