软件工程网

时间:2011-08-10 18:24:21 219次阅读 关键词: sql-server sql 行列转换

有一表，怎么进行如下的行列互相转换：
商品id 商品名称 客户名称 日期 销量
1 a 张三 2009-1-1 1
1 a 李四 2009-1-5 2
2 b 王二 2009-1-5 3
2 b 李四 2009-1-5 5
3 c 王二 2009-1-1 6
1 a 张三 2009-2-1 8
1 a 张三 2009-2-2 2

得到如下格式

一月 二月 合计
日期 a b c 小计 a b c 小计 a b c 小计
张三 1 1 10 10 11 11
李四 2 5 7 2 5 7
王二 3 6 9 3 6 9
累计 3 8 6 17 10 10 13 8 6 27

下面是一个SQL语句实现的例子，这样的问题没有唯一答案，可以自行写SQL语句探索：

--> 生成测试数据: @tb
CREATE TABLE tb (商品id INT,商品名称 VARCHAR(4),客户名称 VARCHAR(4),日期 DATETIME,销量 INT)
INSERT INTO tb
SELECT 1,'a','张三','2009-1-1',1 UNION ALL
SELECT 1,'a','李四','2009-1-5',2 UNION ALL
SELECT 2,'b','王二','2009-1-5',3 UNION ALL
SELECT 2,'b','李四','2009-1-5',5 UNION ALL
SELECT 3,'c','王二','2009-1-1',6 UNION ALL
SELECT 1,'a','张三','2009-2-1',8 UNION ALL
SELECT 1,'a','张三','2009-2-2',2

--SQL查询如下:

DECLARE @sql varchar(8000);
SET @sql = '';

SELECT ID = IDENTITY(int,1,1),CONVERT(varchar(6),日期,112) AS 日期 INTO #tmp1
FROM tb GROUP BY CONVERT(varchar(6),日期,112);

SELECT DISTINCT 商品名称 INTO #tmp2 FROM tb;

DECLARE @id int,@日期 varchar(6);
SET @id = (SELECT TOP 1 id FROM #tmp1 ORDER BY id);
WHILE @id IS NOT NULL
    BEGIN
         SET @日期 = (SELECT 日期 FROM #tmp1 WHERE ID = @id);
         
         SELECT @sql = @sql + ',SUM(CASE WHEN 商品名称 = ''' + 商品名称 
                            + ''' AND CONVERT(varchar(6),日期,112) = ''' + @日期 
                            + ''' THEN 销量 ELSE 0 END) AS [' + @日期 + '_' 
                            + 商品名称 + '销量]'
         FROM #tmp2
         SET @sql = @sql + ',SUM(CASE WHEN CONVERT(varchar(6),日期,112)=''' 
                         + @日期 + ''' THEN 销量 ELSE 0 END) AS [' + @日期 + '_累计销量]'
         SET @id = (SELECT TOP 1 id FROM #tmp1 WHERE id > @id ORDER BY id);
    END

SELECT @sql = @sql + ',SUM(CASE WHEN 商品名称 = ''' + 商品名称 
                   + ''' THEN 销量 ELSE 0 END) AS [' + 商品名称 + '销量]'
FROM #tmp2;

EXEC('SELECT ISNULL(客户名称,''累计'') AS 客户名称'+@sql+',SUM(销量) AS 累计 FROM tb
         GROUP BY 客户名称 WITH ROLLUP')


DROP TABLE #tmp1,#tmp2;
DROP TABLE tb;