如何用SQL Pivot实现动态列转换？关联表数据行转列需求

可以用SQL Pivot实现需求，但由于每个items_id对应的CLASSICTAX记录数量不固定，静态Pivot无法自动生成动态列名，必须结合动态SQL来完成。以下是具体实现步骤（以SQL Server为例）：

1. 示例数据参考 #

JOB_129_FEED_ITEMS表 #

JOB_129_FEED_ITEMS_CLASSICTAX表 #

2. 核心思路 #

先给每个items_id下的CLASSICTAX记录按顺序编号，再通过动态SQL自动生成CLASSICTAX1、CLASSICTAX2这类列名，最后用Pivot完成行转列。

3. 具体SQL代码 #

步骤1：生成带编号的中间数据集 #

给每个items_id的CLASSICTAX记录添加序号，为后续列名生成做准备：

SELECT 
    fi.id,
    fi.SHORT_TITLE,
    c.CLASSICTAX,
    'CLASSICTAX' + CAST(ROW_NUMBER() OVER(PARTITION BY c.items_id ORDER BY c.id) AS VARCHAR(10)) AS ColumnName
FROM JOB_129_FEED_ITEMS fi
JOIN JOB_129_FEED_ITEMS_CLASSICTAX c ON fi.id = c.items_id

步骤2：动态生成Pivot列列表 #

自动获取所有需要生成的动态列名，拼接成可用于Pivot的字符串：

DECLARE @columns NVARCHAR(MAX)
SELECT @columns = STRING_AGG(QUOTENAME(ColumnName), ', ')
FROM (
    SELECT DISTINCT 'CLASSICTAX' + CAST(ROW_NUMBER() OVER(PARTITION BY items_id ORDER BY id) AS VARCHAR(10)) AS ColumnName
    FROM JOB_129_FEED_ITEMS_CLASSICTAX
) t

如果是SQL Server 2017之前的版本，替换为FOR XML PATH方式拼接：

SELECT @columns = STUFF((
    SELECT ', ' + QUOTENAME(ColumnName)
    FROM (
        SELECT DISTINCT 'CLASSICTAX' + CAST(ROW_NUMBER() OVER(PARTITION BY items_id ORDER BY id) AS VARCHAR(10)) AS ColumnName
        FROM JOB_129_FEED_ITEMS_CLASSICTAX
    ) t
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

步骤3：拼接并执行动态Pivot SQL #

把列名代入Pivot语句，执行动态SQL得到最终结果：

DECLARE @pivotSQL NVARCHAR(MAX)
SET @pivotSQL = N'
SELECT id, SHORT_TITLE, ' + @columns + N'
FROM (
    SELECT 
        fi.id,
        fi.SHORT_TITLE,
        c.CLASSICTAX,
        ''CLASSICTAX'' + CAST(ROW_NUMBER() OVER(PARTITION BY c.items_id ORDER BY c.id) AS VARCHAR(10)) AS ColumnName
    FROM JOB_129_FEED_ITEMS fi
    JOIN JOB_129_FEED_ITEMS_CLASSICTAX c ON fi.id = c.items_id
) t
PIVOT (
    MAX(CLASSICTAX)
    FOR ColumnName IN (' + @columns + N')
) p
'

EXEC sp_executesql @pivotSQL

4. 其他数据库适配提示 #

• MySQL：用GROUP_CONCAT生成列名，再通过PREPARE+EXECUTE执行动态SQL
• Oracle：用LISTAGG生成列名，通过EXECUTE IMMEDIATE执行动态SQL

最终生成的结果集结构如下：

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