如何基于EXTRACT(YEAR_MONTH)动态生成列实现行转列（无需CASE）

现有查询与结果 #

当前使用的SQL语句：

SELECT 
  products.sku, 
  SUM(sells_products.quantity) AS quantity, 
  EXTRACT(YEAR_MONTH FROM sells.date) AS period 
FROM sells, sells_products, products 
WHERE sells.id = sells_products.id_sell 
  AND (products.sku = '1111' OR products.sku = '2222') 
  AND sells_products.id_product = products.id 
  AND sells.date BETWEEN '2023-05-01' AND '2023-07-12' 
GROUP BY products.sku, EXTRACT(YEAR_MONTH FROM sells.date)

查询返回的行式结果：

SKU | QNT| PERIOD
1111 | 13 | 202305
1111 | 44 | 202306
1111 | 24 | 202307
2222 | 64 | 202305
2222 | 12 | 202306
2222 | 56 | 202307

期望输出 #

需要将每个period（年月）转为单独的列，且不想手动写CASE语句，希望根据指定的日期区间自动生成对应列：

SKU | 202305 | 202306 | 202307
1111 |   13   |   44   |   24
2222 |   64   |   12   |   56

解决方案 #

纯静态SQL无法实现自动生成动态列，必须借助动态SQL（根据日期区间自动构造查询语句）或数据库自带的透视功能。以下是主流数据库的实现方式：

MySQL 实现（存储过程动态生成透视语句） #

DELIMITER //
CREATE PROCEDURE dynamic_monthly_pivot()
BEGIN
  -- 自动提取日期区间内的所有年月，生成列定义
  SET @cols = NULL;
  SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(CASE WHEN period = ', period, ' THEN quantity ELSE 0 END) AS `', period, '`'))
  INTO @cols
  FROM (
    SELECT DISTINCT EXTRACT(YEAR_MONTH FROM sells.date) AS period
    FROM sells
    WHERE sells.date BETWEEN '2023-05-01' AND '2023-07-12'
  ) AS unique_periods;

  -- 构造完整的透视查询
  SET @sql = CONCAT(
    'SELECT sku, ', @cols, ' FROM (
        SELECT 
          products.sku, 
          SUM(sells_products.quantity) AS quantity, 
          EXTRACT(YEAR_MONTH FROM sells.date) AS period 
        FROM sells
        JOIN sells_products ON sells.id = sells_products.id_sell
        JOIN products ON sells_products.id_product = products.id
        WHERE (products.sku = ''1111'' OR products.sku = ''2222'') 
          AND sells.date BETWEEN ''2023-05-01'' AND ''2023-07-12'' 
        GROUP BY products.sku, period
      ) AS src_data
      GROUP BY sku'
  );

  -- 执行动态生成的SQL
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程获取结果
CALL dynamic_monthly_pivot();

注：这里的CASE语句是动态生成的，无需手动编写每个年月的判断逻辑。

PostgreSQL 实现（crosstab函数+动态SQL） #

首先确保安装tablefunc扩展（若未安装）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后执行动态透视脚本：

DO $$
DECLARE
  column_list text;
BEGIN
  -- 提取日期区间内的所有年月，生成列名列表
  SELECT string_agg(DISTINCT quote_ident(period::text), ', ')
  INTO column_list
  FROM (
    SELECT DISTINCT EXTRACT(YEAR_MONTH FROM sells.date) AS period
    FROM sells
    WHERE sells.date BETWEEN '2023-05-01' AND '2023-07-12'
  ) AS unique_periods;

  -- 动态执行crosstab透视查询
  EXECUTE format(
    'SELECT * FROM crosstab(
      ''SELECT sku, period::text, quantity FROM (
          SELECT 
            products.sku, 
            EXTRACT(YEAR_MONTH FROM sells.date) AS period, 
            SUM(sells_products.quantity) AS quantity 
          FROM sells
          JOIN sells_products ON sells.id = sells_products.id_sell
          JOIN products ON sells_products.id_product = products.id
          WHERE (products.sku = ''''1111'''' OR products.sku = ''''2222'''') 
            AND sells.date BETWEEN ''''2023-05-01'''' AND ''''2023-07-12'''' 
          GROUP BY products.sku, period
        ) AS src_data
        ORDER BY sku, period'',
      ''SELECT DISTINCT EXTRACT(YEAR_MONTH FROM sells.date)::text FROM sells WHERE sells.date BETWEEN ''''2023-05-01'''' AND ''''2023-07-12'''' ORDER BY 1''
    ) AS pivot_result(sku text, %s);', column_list
  );
END $$;

SQL Server 实现（动态PIVOT） #

DECLARE @column_list NVARCHAR(MAX);
DECLARE @pivot_sql NVARCHAR(MAX);

-- 提取日期区间内的所有年月，生成列名（带引号）
SELECT @column_list = STRING_AGG(DISTINCT QUOTENAME(FORMAT(sells.date, 'yyyyMM')), ', ')
FROM sells
WHERE sells.date BETWEEN '2023-05-01' AND '2023-07-12';

-- 构造动态PIVOT查询语句
SET @pivot_sql = N'
SELECT sku, ' + @column_list + '
FROM (
    SELECT 
      products.sku, 
      SUM(sells_products.quantity) AS quantity, 
      FORMAT(sells.date, ''yyyyMM'') AS period 
    FROM sells
    JOIN sells_products ON sells.id = sells_products.id_sell
    JOIN products ON sells_products.id_product = products.id
    WHERE (products.sku = ''1111'' OR products.sku = ''2222'') 
      AND sells.date BETWEEN ''2023-05-01'' AND ''2023-07-12'' 
    GROUP BY products.sku, FORMAT(sells.date, ''yyyyMM'')
) AS src_data
PIVOT (
  SUM(quantity)
  FOR period IN (' + @column_list + ')
) AS pivot_result;';

-- 执行动态SQL
EXEC sp_executesql @pivot_sql;

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