SQL动态行转列：将表中唯一log_ID字段转置为动态命名列

核心实现逻辑：先对除log_ID外的所有重复字段分组，给每组内的log_ID按序生成序号，再根据序号动态拼接聚合逻辑，自动生成对应数量的log_ID_n列，无需提前固定log_ID的数量。

MySQL 实现 #

-- 第一步：动态生成需要拼接的log_ID列逻辑
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    'MAX(CASE WHEN rn = ', rn, ' THEN log_ID END) AS log_ID_', rn
  )
) INTO @sql
FROM (
  SELECT ROW_NUMBER() OVER(PARTITION BY 字段1,字段2,字段3... ORDER BY log_ID) AS rn 
  FROM 你的表名
) t;

-- 第二步：拼接完整查询SQL并执行
SET @sql = CONCAT('SELECT 字段1,字段2,字段3..., ', @sql, ' 
                  FROM (
                    SELECT *, ROW_NUMBER() OVER(PARTITION BY 字段1,字段2,字段3... ORDER BY log_ID) AS rn 
                    FROM 你的表名
                  ) t 
                  GROUP BY 字段1,字段2,字段3...');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意：将上述代码中的字段1,字段2,字段3...替换为你实际表中除log_ID外的所有重复字段即可。

PostgreSQL 实现 #

DO $$
DECLARE 
    col_list text;
    query_sql text;
BEGIN
    -- 生成动态列名列表
    SELECT string_agg(DISTINCT format('log_ID_%s', rn), ', ')
    INTO col_list
    FROM (
        SELECT row_number() OVER(PARTITION BY 字段1,字段2,字段3... ORDER BY log_ID) AS rn
        FROM 你的表名
    ) t;

    -- 构造完整查询语句
    query_sql := format('
        SELECT *
        FROM crosstab(
            ''SELECT 字段1,字段2,字段3..., row_number() OVER(PARTITION BY 字段1,字段2,字段3... ORDER BY log_ID) rn, log_ID FROM 你的表名 ORDER BY 1'',
            ''SELECT generate_series(1, max(rn)) FROM (SELECT row_number() OVER(PARTITION BY 字段1,字段2,字段3...) rn FROM 你的表名) t''
        ) AS ct (字段1 字段类型, 字段2 字段类型, 字段3 字段类型, %s)
    ', col_list);

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

SQL Server 实现 #

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

-- 生成动态列
SELECT @cols = STUFF((SELECT ',' + QUOTENAME('log_ID_' + CAST(rn AS VARCHAR(10)))
                    FROM (
                        SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY 字段1,字段2,字段3... ORDER BY log_ID) rn
                        FROM 你的表名
                    ) t
                    ORDER BY rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

-- 构造查询语句
SET @query = 'SELECT 字段1,字段2,字段3..., ' + @cols + ' 
            FROM (
                SELECT *, ''log_ID_'' + CAST(ROW_NUMBER() OVER(PARTITION BY 字段1,字段2,字段3... ORDER BY log_ID) AS VARCHAR(10)) col
                FROM 你的表名
            ) x
            PIVOT (
                MAX(log_ID)
                FOR col IN (' + @cols + ')
            ) p '

EXECUTE(@query)

通用说明 #

如果是Hive/Spark SQL等其他SQL引擎，逻辑和MySQL方案一致，直接使用动态SQL拼接即可实现，无需调整核心逻辑。

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