将部分相同的多行记录转成一行多列

数据库环境：SQL SERVER2008R2

需求如下图：

简单解释一下需求，将同一年月的多行转到一行，分别展示每个用户的信息，在最后分别对前面数量和金额进行合计。

这其实又是行列转换的一个应用，下面直接贴SQL

/*数据准备*/
WITH    x0
          AS ( SELECT   CONVERT(DATE, '2015-01-01') AS 日期 ,
                        N'小1' AS 姓名 ,
                        100 AS 数量 ,
                        1000 AS 金额
               UNION ALL
               SELECT   CONVERT(DATE, '2015-01-01') AS 日期 ,
                        N'小2' AS 姓名 ,
                        200 AS 数量 ,
                        2100 AS 金额
               UNION ALL
               SELECT   CONVERT(DATE, '2015-01-02') AS 日期 ,
                        N'小3' AS 姓名 ,
                        300 AS 数量 ,
                        4000 AS 金额
               UNION ALL
               SELECT   CONVERT(DATE, '2015-01-02') AS 日期 ,
                        N'小4' AS 姓名 ,
                        600 AS 数量 ,
                        7000 AS 金额
               UNION ALL
               SELECT   CONVERT(DATE, '2015-01-02') AS 日期 ,
                        N'小4' AS 姓名 ,
                        700 AS 数量 ,
                        8000 AS 金额
               UNION ALL
               SELECT   CONVERT(DATE, '2015-01-02') AS 日期 ,
                        N'小5' AS 姓名 ,
                        500 AS 数量 ,
                        4600 AS 金额
             ),/*将同一人的信息合并*/
        x1
          AS ( SELECT   日期 ,
                        姓名 ,
                        SUM(数量) AS 数量 ,
                        SUM(金额) AS 金额
               FROM     x0
               GROUP BY 日期 ,
                        姓名
             )
    /*将基础数据插入到临时表*/
    SELECT  a.日期 ,
            a.姓名 ,
            a.数量 ,
            a.金额 ,
            ROW_NUMBER() OVER ( PARTITION BY a.日期 ORDER BY a.姓名 ) 序号
    INTO    #t
    FROM    x1 a 
/*拼接SQL,实现动态行列转换*/
DECLARE @sql NVARCHAR(MAX);   
SET @sql = N'';
SELECT  @sql = @sql + N',max(case 序号 when ' + CAST(tt.序号 AS VARCHAR)
        + N' then 姓名 else null end) 姓名' + N',max(case 序号 when '
        + CAST(tt.序号 AS VARCHAR) + N' then 数量 else null end) 数量'
        + N',max(case 序号 when ' + CAST(tt.序号 AS VARCHAR)
        + N' then 金额 else null end) 金额'
FROM    ( SELECT DISTINCT
                    序号
          FROM      #t
        ) tt
ORDER BY 序号;
SET @sql = N'select 日期' + @sql
    + N',sum(数量) as 数量合计,sum(金额) as 金额合计 from #t group by 日期';
EXEC(@sql);

最终实现结果截图如下

这个需求并不难，重点在掌握动态行列转换的使用。

（本文完）