对透视表的分析

时间：2011-03-07 来源：人生无聊

写在前面：知其然也要知其所以然

　　今天一同事去公司面试，回来向大家提了个问题，问题如下：有Table字段如下：_date,_num，如何生成如下格式：

当时大家都在群里讨论，我也随手写了一个，但是不太好。SQL如下：

--假設數據格式為：_date:2011/03/01  _num:10
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

当时一个同事在网上找了段代码改了下如下：

CREATE TABLE Test
(
    _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],