如何无需硬编码日期实现SQL数据透视（计算每日quan*cst总和）

你现在的需求是把每日的quan*cst汇总值按pid横向展示，之前的硬编码日期方式虽然能实现，但一旦新增日期就需要修改SQL语句，确实不够灵活。下面给你两种更简便的实现方式，完全不用硬编码日期：

方法一：使用PIVOT函数（适配已知日期，可结合动态SQL实现全动态） #

如果你的SQL版本支持PIVOT（比如SQL Server 2005及以上），可以用这个函数简化列转行的逻辑，不用手动写每个日期的子查询：

SELECT pid, [2017-05-01] AS day1sale, [2017-05-02] AS day2sale
FROM (
    SELECT pid, date, SUM(quan*cst) AS daily_sale
    FROM dbo.test1
    GROUP BY pid, date
) AS SourceTable
PIVOT (
    SUM(daily_sale)
    FOR date IN ([2017-05-01], [2017-05-02])
) AS PivotTable
ORDER BY pid;

注：如果要让列名自动适配所有日期，看下面的动态SQL方案。

方法二：动态SQL实现完全自动适配所有日期 #

如果数据表会持续新增日期，想要SQL自动识别所有日期并生成对应列，可以用动态SQL拼接查询语句：

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

-- 自动生成所有不重复日期的列名（格式为[yyyy-mm-dd]）
SET @cols = STUFF((SELECT ',' + QUOTENAME(date)
                   FROM (SELECT DISTINCT date FROM dbo.test1) AS Dates
                   ORDER BY date
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 拼接完整的PIVOT查询语句
SET @query = 'SELECT pid, ' + @cols + ' 
              FROM (
                  SELECT pid, date, SUM(quan*cst) AS daily_sale
                  FROM dbo.test1
                  GROUP BY pid, date
              ) AS SourceTable
              PIVOT (
                  SUM(daily_sale)
                  FOR date IN (' + @cols + ')
              ) AS PivotTable
              ORDER BY pid;';

-- 执行动态生成的SQL
EXEC sp_executesql @query;

这个方案会自动读取dbo.test1中所有不重复的日期，把它们作为横向列名，每个列对应该日期的quan*cst汇总值，新增日期后重新执行SQL就能自动适配，完全不用修改代码。

另外提一句：你之前的SQL里最后GROUP BY t1.pid,m2,m1其实是多余的，因为子查询已经按pid分组，左连接后每个pid只会对应一条记录，不需要再分组。

内容的提问来源于stack exchange，提问作者Dennyc