多种sql技术的综合应用（行转列、定制、批量数据产生）

/*
功能：按报表格式显示（行转列、定制、批量数据产生）
作者：王召冠
时间：2010-01-25 20:10
说明：此例子也是多种sql技术的综合应用

问题：假设有张考勤表(tb)如下:
姓名    　考勤日期    　　考勤次数
张三    2010-01-01        2
张三    2010-01-02        3
张三    2010-01-03        1
张三    2010-01-01        1
李四    2010-01-02        2
李四    2010-01-03        2

想变成(得到如下结果)：按指定时间段显示考勤报表
姓名  2010-01-01 2010-01-02 2010-01-03 2010-01-04  ... 汇总     
---- ---------- ---------- ---------- ----------      ----
李四        0           2          2          0        4
张三        3           3          1          0        7
汇总        3           5          3          0        11
----------------------------------------------------------
*/

CREATE TABLE tb(vName NVARCHAR(10), dtDate DATETIME, iNum INT)
GO

INSERT  INTO tb 
VALUES  ( '张三', '2010-01-01', 2 )
INSERT  INTO tb
VALUES  ( '张三', '2010-01-02', 3 )
INSERT  INTO tb
VALUES  ( '张三', '2010-01-03', 1 )
INSERT  INTO tb
VALUES  ( '张三', '2010-01-01', 1 )
INSERT  INTO tb
VALUES  ( '李四', '2010-01-02', 2 )
INSERT  INTO tb
VALUES  ( '李四', '2010-01-03', 2 )
GO

--SELECT    *
--FROM    tb
    
--SQL SERVER 动态SQL
DECLARE @dtBegin DATETIME, @dtEnd DATETIME
SELECT    @dtBegin = '2010-01-01', @dtEnd = '2010-01-31'

SET NOCOUNT ON

/*
此处代码功能说明：
    根据指定的时间段，快速生成一个不间断的时间序列表
具体实现特性说明：
    每循环一次生成2的n次方条记录，减少循环次数。
注：
    从实际应用上来说，在此处没有太明显的效果和意义；仅仅体现一种优化算法而已。
*/
DECLARE @iRank INT 
SET @iRank = 1
DECLARE @tmp TABLE(dtDate DATETIME)

INSERT INTO @tmp ( dtDate ) VALUES ( @dtBegin ) 
WHILE DATEADD(DAY, POWER(2, (@iRank-1)), @dtBegin) <= @dtEnd
BEGIN
    INSERT INTO @tmp ( dtDate )
    SELECT    DATEADD(DAY, POWER(2, (@iRank-1)), dtDate)
    FROM    @tmp 
    WHERE    DATEADD(DAY, POWER(2, @iRank-1), dtDate) <= @dtEnd
    
    SET @iRank = @iRank + 1
END

/*
此处代码功能说明：
    根据时间序列表，生成动态SQL
注：
    之所以将动态sql分到两个变量里，主要是尽量避免8000个字符的限制
*/
DECLARE @sql_a VARCHAR(8000)
SET @sql_a = 'select vName'
SELECT  @sql_a = @sql_a 
                + ', sum(case dtDate when ''' + CONVERT(NVARCHAR(10), dtDate, 120)
                + ''' then iNum else 0 end) [' + CONVERT(NVARCHAR(10), dtDate, 120) + ']'
FROM    @tmp AS a
SET @sql_a = @sql_a + ', sum(iNum) as iTotal from tb group by vName'
--EXEC(@sql_a)

DECLARE @sql_b VARCHAR(8000)
SET @sql_b = 'select ''total'''
SELECT    @sql_b = @sql_b + ', isnull((select sum(iNum) from tb where dtDate=''' + CONVERT(NVARCHAR(10), dtDate, 120) + '''), 0)'
FROM    @tmp
ORDER BY dtDate
SET @sql_b = @sql_b + ', (select sum(iNum) from tb)'
--EXEC(@sql_b)
EXEC('select * from (' + @sql_a + ' union ' + @sql_b + ') as a order by a.iTotal') 
GO