对透视表的分析
写在前面：知其然也要知其所以然
今天一同事去公司面试，回来向大家提了个问题，问题如下：有Table字段如下：_date,_num，如何生成如下格式：
当时大家都在群里讨论，我也随手写了一个，但是不太好。SQL如下：
insert test ..t
values ('2010/03/07',6)
select * from test..t
use test
create table tm_table(
yy nvarchar(5),
m01 nvarchar(10),
m02 nvarchar(10),
m03 nvarchar(10),
m04 nvarchar(10),
m05 nvarchar(10),
m06 nvarchar(10),
m07 nvarchar(10),
m08 nvarchar(10),
m09 nvarchar(10),
m10 nvarchar(10),
m11 nvarchar(10),
m12 nvarchar(10)
)
select * from tm_table
SELECT substring(_DATE,1,7) _date,SUM(_NUM) _num INTO #T FROM test..T GROUP BY substring(_DATE,1,7)
insert into tm_table (yy)
select substring(_date,1,4) yys from t group by substring(_date,1,4)
declare @date nvarchar(7)
declare @num int
declare @mm nvarchar(3)
declare @yy nvarchar(4)
declare @sql nvarchar(50)
declare list CURSOR Local KeySet FOR SELECT * FROM #T
OPEN list
fetch first from list into @date ,@num
while (@@fetch_status <> -1)
BEGIN
set @mm='m'+substring (@date,6,2)
set @yy=substring (@date,1,4)
print @mm
print @yy
set @sql='update tm_table set '+@mm+'='+convert(nvarchar,@num)+' where yy='+@yy
exec (@sql)
fetch next from list into @date ,@num
end
CLOSE list
DEALLOCATE list
select * from tm_table order by yy
当时一个同事在网上找了段代码改了下如下：
(
_date DATETIME,
_num INT
)
INSERT sz6_oth.magic.Test
SELECT '2011-01-01',100 UNION
SELECT '2011-02-01',100 UNION
SELECT '2011-03-01',100 UNION
SELECT '2011-04-01',200 UNION
SELECT '2011-05-01',300 UNION
SELECT '2011-06-01',400
SELECT YEAR(_date) YY,MONTH(_date) MM,SUM(t._num) NUM
INTO xx
FROM test t
GROUP BY YEAR(_date) ,MONTH(_date)
DECLARE @sql VARCHAR(8000)
set @sql = 'SELECT [yy], '
select @sql = @sql +
'sum(case mm when '+quotename(mm,'''')+' then num else 0 end) as ' + quotename(mm)+',' from xx group by mm
print @sql
print left(@sql,len(@sql)-1)
select @sql = left(@sql,len(@sql)-1)
select @sql = @sql + ' from xx group by [yy]'
select @sql
print @sql
exec(@sql)
这是个典型的透视表的生成，我以前也知道，但是碍于不想套用，写了上面的Sql，分析下同事的这个Sql，比我的简洁多了，分析了下他的代码，主要先对Year做主分组，然后用M(月)做另外的分组，主要语句是下面这句：
select 'sum(case mm when '+quotename(mm,'''')+' then num else 0 end) as ' + quotename(mm)+',' from xx group by mm
这句将要分组的表头给select 出来，结果如下：
然后select @sql = @sql +
'sum(case mm when '+quotename(mm,'''')+' then num else 0 end) as ' + quotename(mm)+',' from xx group by mm
在这里发现一个奇妙的用法，就是上面贴图的六笔数据，在上面这个语句中直接可以相加，相加结果如下：
SELECT [yy],
sum(case mm when '1' then num else 0 end) as [1],
sum(case mm when '2' then num else 0 end) as [2],
sum(case mm when '3' then num else 0 end) as [3],
sum(case mm when '4' then num else 0 end) as [4],
sum(case mm when '5' then num else 0 end) as [5],
sum(case mm when '6' then num else 0 end) as [6],
这个巧妙的相加就加成了上面的语句,再结合全部语句,最后sql 语句如下：
SELECT [yy],
sum(case mm when '1' then num else 0 end) as [1],
sum(case mm when '2' then num else 0 end) as [2],
sum(case mm when '3' then num else 0 end) as [3],
sum(case mm when '4' then num else 0 end) as [4],
sum(case mm when '5' then num else 0 end) as [5],
sum(case mm when '6' then num else 0 end) as [6]
from xx group by [yy]
这种写法对这个表头可变的因素很灵活，如果mm增加了，如：13或者14等，出来的表头也会多出来，这也是这个写法的有点。
写出这篇文章另一个目的，是纪念我写的那个Sql语句，因为它以后将不会出现在我的Sql语句中了，哈哈！
