固定一列并转置其他列：SQL实现透视表需求

原数据表格 #

目标透视表效果 #

实现方案 #

1. 静态日期列（已知所有需要转换的日期） #

如果需要转换的日期是固定值，用条件聚合是所有SQL数据库通用的解决方案。通过CASE WHEN匹配日期，结合聚合函数（MAX/MIN/SUM均可，这里因每个Unique #+日期仅一条数据，选MAX即可）完成透视：

SELECT
  `Unique #`,
  MAX(CASE WHEN DATE(Date) = '2022-01-01' THEN Cost END) AS `2022-01-01`,
  MAX(CASE WHEN DATE(Date) = '2022-01-04' THEN Cost END) AS `2022-01-04`,
  -- 新增日期可继续添加对应CASE语句
  '--' AS `...`
FROM your_table
GROUP BY `Unique #`
ORDER BY `Unique #`;

2. 动态日期列（日期不固定，自动生成列） #

如果日期是动态变化的，需根据数据库类型采用对应动态SQL方案：

MySQL 实现

通过预处理语句动态拼接SQL：

SET @sql = NULL;
-- 拼接所有日期对应的CASE语句
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN DATE(Date) = ''',
      DATE(Date),
      ''' THEN Cost END) AS `',
      DATE(Date),
      '`'
    )
  ) INTO @sql
FROM your_table;

-- 组合完整查询语句
SET @sql = CONCAT('SELECT `Unique #`, ', @sql, ', ''--'' AS `...` FROM your_table GROUP BY `Unique #` ORDER BY `Unique #`');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 实现

使用PIVOT运算符结合动态SQL：

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

-- 获取所有唯一日期并转成列名格式
SET @cols = STUFF((SELECT ',' + QUOTENAME(CAST(Date AS DATE))
                  FROM your_table
                  GROUP BY CAST(Date AS DATE)
                  ORDER BY CAST(Date AS DATE)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'');

-- 拼接PIVOT查询语句
SET @query = 'SELECT "Unique #", ' + @cols + ', ''--'' AS "..." 
              FROM (
                    SELECT "Unique #", Cost, CAST(Date AS DATE) AS DateOnly
                    FROM your_table
                   ) AS src
              PIVOT (
                    MAX(Cost)
                    FOR DateOnly IN (' + @cols + ')
                   ) AS pvt
              ORDER BY "Unique #";';

-- 执行动态SQL
EXECUTE sp_executesql @query;

PostgreSQL 实现

使用crosstab函数（需先启用tablefunc扩展）：

-- 仅需执行一次的扩展启用操作
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 执行透视查询
SELECT *
FROM crosstab(
  'SELECT "Unique #", DATE(Date), Cost FROM your_table ORDER BY 1,2',
  'SELECT DISTINCT DATE(Date) FROM your_table ORDER BY 1'
) AS ct(
  "Unique #" INT,
  "2022-01-01" NUMERIC,
  "2022-01-04" NUMERIC,
  "..." TEXT
);

若需动态生成列定义，可编写PL/pgSQL存储过程实现。

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