CREATE TABLE tb(Item varchar(10),Color varchar(10),Quantity int)
INSERT tb SELECT 'Table','Blue',  124
UNION ALL SELECT 'Table','Red',   60
UNION ALL SELECT 'Table','Red',   99
UNION ALL SELECT 'Table','Green', 120
UNION ALL SELECT 'Table','Green', 110
UNION ALL SELECT 'Chair','Yellow',101
UNION ALL SELECT 'Chair','Yellow',131
UNION ALL SELECT 'Chair','Red',   90
UNION ALL SELECT 'Chair','White', 55
GO

--生成分組信息臨時表
SELECT Item,Color,id=0
INTO # FROM tb
GROUP BY Item,Color
--生成各組Item的Color序號
DECLARE @Item varchar(10),@i varchar(10)
UPDATE # SET 
	@i=CASE WHEN @Item=Item THEN @i+1 ELSE 1 END,
	id=@i,@Item=Item

--生成交叉數(shù)據(jù)報表處理語句
DECLARE @s1 nvarchar(4000),@s2 nvarchar(4000),@fd nvarchar(4000)
SELECT @s1='',@s2='',@fd='',@i=MAX(id)
FROM #
GROUP BY Item
WHILE @i>0
	SELECT 
		@fd=','+QUOTENAME('col'+@i)+'='+QUOTENAME(@i)+@fd,
		@s1=@s1+','+QUOTENAME(@i)
			+'=MAX(CASE id WHEN '+@i
			+' THEN Color END)',
		@s2=@s2+','+QUOTENAME(@i)
			+'=CAST(SUM(CASE b.id WHEN '+@i
			+' THEN a.Quantity END) as varchar)',
		@i=@i-1
EXEC('
SELECT Item=Item'+@fd+' FROM(
	SELECT Item'+@s1+',s=0 FROM # GROUP BY Item
	UNION ALL
	SELECT a.Item'+@s2+',s=1 
	FROM tb a,# b
	WHERE a.Item=b.Item AND a.Color=b.Color
	GROUP BY a.Item
)a ORDER BY Item,s')
DROP TABLE #
/*--結(jié)果
Item       col0         col1         col2 
------------- ------------------- ----------------- --------------- 
Chair      Red         White       Yellow
Chair      90          55          232
Table      Blue        Green       Red
Table      124         230         159
--*/