又一个行列转换
有一表
商品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
--> 生成测试数据: @tb
DECLARE @tb TABLE (商品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查询如下:
SELECT ISNULL(客户名称,'累计') AS 客户名称,
SUM(CASE WHEN 商品名称 = 'a' AND CONVERT(varchar(6),日期,112)='200901'
THEN 销量 ELSE 0 END) AS [200901_a销量],
SUM(CASE WHEN 商品名称 = 'b' AND CONVERT(varchar(6),日期,112)='200901'
THEN 销量 ELSE 0 END) AS [200901_b销量],
SUM(CASE WHEN 商品名称 = 'c' AND CONVERT(varchar(6),日期,112)='200901'
THEN 销量 ELSE 0 END) AS [200901_c销量],
SUM(CASE WHEN CONVERT(varchar(6),日期,112)='200901'
THEN 销量 ELSE 0 END) AS [200901_累计销量],
SUM(CASE WHEN 商品名称 = 'a' AND CONVERT(varchar(6),日期,112)='200902'
THEN 销量 ELSE 0 END) AS [200902_a销量],
SUM(CASE WHEN 商品名称 = 'b' AND CONVERT(varchar(6),日期,112)='200902'
THEN 销量 ELSE 0 END) AS [200902_b销量],
SUM(CASE WHEN 商品名称 = 'c' AND CONVERT(varchar(6),日期,112)='200902'
THEN 销量 ELSE 0 END) AS [200902_c销量],
SUM(CASE WHEN CONVERT(varchar(6),日期,112)='200902'
THEN 销量 ELSE 0 END) AS [200902_累计销量],
SUM(CASE WHEN 商品名称 = 'a'
THEN 销量 ELSE 0 END) AS [a销量],
SUM(CASE WHEN 商品名称 = 'b'
THEN 销量 ELSE 0 END) AS [b销量],
SUM(CASE WHEN 商品名称 = 'c'
THEN 销量 ELSE 0 END) AS [b销量],
SUM(销量) AS [累计销量]
FROM @tb
GROUP BY 客户名称 WITH ROLLUP
--> 生成测试数据: @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;
--> 生成测试数据: @tb --drop table 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
select distinct a.商品id,a.商品名称,b.日期 into #a from tb a
cross join(select distinct CONVERT(varchar(6),日期,112) as 日期 from tb)b
declare @sql nvarchar(4000)
SET @sql=N'select isnull([客户名称],N''总计'') as 客户名称' --初始化变量必须
select @sql=@sql+N','+
QUOTENAME(日期+N'_'+a.商品名称+N'销量')+
N'=sum(
case when [商品名称]='+quotename(a.商品名称,N'''')+
N'and convert(varchar(6),日期,112)='+quotename(convert(varchar(6),日期,112),N'''')
+N' then 销量 else 0 end)'
+case when not exists(select 1 from #a where 日期=a.日期 and 商品id>a.商品id) then
+N','+QUOTENAME(日期+N'合计销量')
+N'=sum
(case when convert(varchar(6),日期,112)='+quotename(日期,N'''')
+N' then 销量 else 0 end)'
else N''
end
from #a a
group by 日期,商品Id,商品名称
select @sql=@sql+N','+
QUOTENAME(商品名称+N'_累计')+
N'=sum
(case when 商品名称='+QUOTENAME(商品名称,N'''')+N' then 销量 else 0 end)'
from #a group by 商品名称
select @sql=@sql+N',''总计''=sum(销量) from tb group by 客户名称 with rollup'
print @sql
exec(@sql)
/*
select isnull([客户名称],N'总计') as 客户名称,[200901_a销量]=sum(
case when [商品名称]='a'and convert(varchar(6),日期,112)='200901' then 销量 else 0 end),[200901_b销量]=sum(
case when [商品名称]='b'and convert(varchar(6),日期,112)='200901' then 销量 else 0 end),[200901_c销量]=sum(
case when [商品名称]='c'and convert(varchar(6),日期,112)='200901' then 销量 else 0 end),[200901合计销量]=sum
(case when convert(varchar(6),日期,112)='200901' then 销量 else 0 end),[200902_a销量]=sum(
case when [商品名称]='a'and convert(varchar(6),日期,112)='200902' then 销量 else 0 end),[200902_b销量]=sum(
case when [商品名称]='b'and convert(varchar(6),日期,112)='200902' then 销量 else 0 end),[200902_c销量]=sum(
case when [商品名称]='c'and convert(varchar(6),日期,112)='200902' then 销量 else 0 end),[200902合计销量]=sum
(case when convert(varchar(6),日期,112)='200902' then 销量 else 0 end),[a_累计]=sum
(case when 商品名称='a' then 销量 else 0 end),[b_累计]=sum
(case when 商品名称='b' then 销量 else 0 end),[c_累计]=sum
(case when 商品名称='c' then 销量 else 0 end),'总计'=sum(销量) from tb group by 客户名称 with rollup
--结果
客户名称 200901_a销量 200901_b销量 200901_c销量 200901合计销量 200902_a销量 200902_b销量
--200902_c销量 200902合计销量 a_累计 b_累计 c_累计 总计
李四 2 5 0 7 0 0 0 0 2 5 0 7
王二 0 3 6 9 0 0 0 0 0 3 6 9
张三 1 0 0 1 10 0 0 10 11 0 0 11
总计 3 8 6 17 10 0 0 10 13 8 6 27
*/
