日期:2014-05-18 浏览次数:20468 次
CREATE TABLE t1 ( nian INT, yue INT, amount DECIMAL(18,1) ) INSERT INTO t1 SELECT 1991, 1, 1.1 UNION ALL SELECT 1991, 2, 1.2 UNION ALL SELECT 1991, 3, 1.3 UNION ALL SELECT 1991, 4, 1.4 UNION ALL SELECT 1992, 1, 2.1 UNION ALL SELECT 1992, 2, 2.2 UNION ALL SELECT 1992, 3, 2.3 UNION ALL SELECT 1992, 4, 2.4 SELECT * FROM t1 DECLARE @str VARCHAR(8000) SET @str='select nian' SELECT @str=@str+',max(case when yue='+LTRIM(yue)+' then amount else 0 end) as ['+LTRIM(yue)+']' FROM (SELECT DISTINCT yue FROM t1) AS a1 SET @str=@str+' from t1 group by nian' PRINT @str EXEC (@str) ----------------- nian 1 2 3 4 1991 1.1 1.2 1.3 1.4 1992 2.1 2.2 2.3 2.4
------解决方案--------------------
字段名调整了下
DECLARE @str VARCHAR(8000) SET @str='select nian' SELECT @str=@str+',max(case when yue='+LTRIM(yue)+' then amount else 0 end) as [m'+LTRIM(yue)+']' FROM (SELECT DISTINCT yue FROM t1) AS a1 SET @str=@str+' from t1 group by nian' PRINT @str EXEC (@str) --------------------- nian m1 m2 m3 m4 1991 1.1 1.2 1.3 1.4 1992 2.1 2.2 2.3 2.4