如何基于Description列唯一值动态转置TrendTable表数据？

问题背景 #

现有TrendTable表结构及数据如下：

TS                 |    Description    |    Value
2023-02-28 10:42        CLAF                 172
2023-02-28 10:42        CLAF3                119
2023-02-28 10:42        CLAF6                114
2023-02-28 10:42        CLAF8                193
2023-02-28 10:42        CLAF9                163
2023-02-28 10:42        CLAF1                132
2023-02-28 10:43        CLAF                 88
2023-02-28 10:43        CLAF3                93
2023-02-28 10:43        CLAF6                79
2023-02-28 10:43        CLAF8                153
2023-02-28 10:43        CLAF9                109
2023-02-28 10:43        CLAF1                125

用户最初尝试了静态行转列的SQL：

SELECT TS,
MAX(CASE WHEN Description ='CLAF' THEN Value END) AS "CLAF"
FROM TrendTable
GROUP BY TS
Order By TS

但由于Description列的取值不固定，需要基于该列所有唯一值动态生成列，最终得到按TS分组的行转列结果：

TS                 |    CLAF    |    CLAF3    |    CLAF6    | CLAF8   |   CLAF9   |   CLAF1
2023-02-28 10:42        172            119          114        193        163         132
2023-02-28 10:43        88             93           79         153        109         125

分数据库解决方案 #

1. SQL Server（动态SQL实现） #

通过拼接字符串生成包含所有Description唯一值的查询逻辑，再执行动态SQL：

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

-- 拼接所有唯一的Description作为列名
SELECT @cols = STRING_AGG(QUOTENAME(Description), ', ')
FROM (SELECT DISTINCT Description FROM TrendTable) AS Descriptions;

-- 生成动态查询语句
SET @query = N'
SELECT TS, ' + @cols + N'
FROM (
    SELECT TS, Description, Value
    FROM TrendTable
) AS SourceTable
PIVOT (
    MAX(Value)
    FOR Description IN (' + @cols + N')
) AS PivotTable
ORDER BY TS;';

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

如果是SQL Server 2017及更早版本（不支持STRING_AGG），可以用FOR XML PATH拼接列名：

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

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Description)
                      FROM (SELECT DISTINCT Description FROM TrendTable) AS Descriptions
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = N'
SELECT TS, ' + @cols + N'
FROM (
    SELECT TS, Description, Value
    FROM TrendTable
) AS SourceTable
PIVOT (
    MAX(Value)
    FOR Description IN (' + @cols + N')
) AS PivotTable
ORDER BY TS;';

EXEC sp_executesql @query;

2. MySQL（预处理语句实现） #

MySQL通过预处理语句动态生成行转列逻辑：

SET @sql = NULL;

-- 拼接列对应的CASE逻辑
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'MAX(CASE WHEN Description = ''',
        Description,
        ''' THEN Value END) AS ',
        QUOTE(Description)
    )
) INTO @sql
FROM TrendTable;

-- 生成完整查询语句
SET @sql = CONCAT('SELECT TS, ', @sql, ' FROM TrendTable GROUP BY TS ORDER BY TS;');

-- 预处理并执行
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

3. PostgreSQL（两种实现方式） #

方法1：借助crosstab函数

首先需要启用tablefunc扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后通过动态SQL生成查询：

DO $$
DECLARE
    cols text;
    query text;
BEGIN
    -- 获取所有唯一的Description列名
    SELECT string_agg(DISTINCT quote_ident(Description), ', ') INTO cols
    FROM TrendTable;

    -- 生成crosstab查询语句
    query := format('
        SELECT *
        FROM crosstab(
            ''SELECT TS, Description, Value FROM TrendTable ORDER BY 1,2'',
            ''SELECT DISTINCT Description FROM TrendTable ORDER BY 1''
        ) AS ct(TS timestamp, %s);', cols);

    -- 执行查询
    EXECUTE query;
END $$;

方法2：动态拼接CASE语句

DO $$
DECLARE
    cols text;
    query text;
BEGIN
    SELECT string_agg(DISTINCT format('MAX(CASE WHEN Description = %L THEN Value END) AS %I', Description, Description), ', ') INTO cols
    FROM TrendTable;

    query := format('SELECT TS, %s FROM TrendTable GROUP BY TS ORDER BY TS;', cols);
    EXECUTE query;
END $$;

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