SQL Server如何对Comments表行转列生成指定格式透视表

核心实现逻辑 #

• 先对每个NCC_CLTID分组内的记录按顺序生成1-20的序号
• 再通过条件聚合按序号映射为TYPE1到TYPE20列，无对应值的位置自动返回NULL

通用写法（兼容MySQL8.0+、PostgreSQL、SQL Server、Oracle等支持窗口函数的数据库） #

SELECT
    NCC_CLTID,
    MAX(CASE WHEN rn = 1 THEN NCC_CTYPE END) AS TYPE1,
    MAX(CASE WHEN rn = 2 THEN NCC_CTYPE END) AS TYPE2,
    MAX(CASE WHEN rn = 3 THEN NCC_CTYPE END) AS TYPE3,
    MAX(CASE WHEN rn = 4 THEN NCC_CTYPE END) AS TYPE4,
    MAX(CASE WHEN rn = 5 THEN NCC_CTYPE END) AS TYPE5,
    MAX(CASE WHEN rn = 6 THEN NCC_CTYPE END) AS TYPE6,
    MAX(CASE WHEN rn = 7 THEN NCC_CTYPE END) AS TYPE7,
    MAX(CASE WHEN rn = 8 THEN NCC_CTYPE END) AS TYPE8,
    MAX(CASE WHEN rn = 9 THEN NCC_CTYPE END) AS TYPE9,
    MAX(CASE WHEN rn = 10 THEN NCC_CTYPE END) AS TYPE10,
    MAX(CASE WHEN rn = 11 THEN NCC_CTYPE END) AS TYPE11,
    MAX(CASE WHEN rn = 12 THEN NCC_CTYPE END) AS TYPE12,
    MAX(CASE WHEN rn = 13 THEN NCC_CTYPE END) AS TYPE13,
    MAX(CASE WHEN rn = 14 THEN NCC_CTYPE END) AS TYPE14,
    MAX(CASE WHEN rn = 15 THEN NCC_CTYPE END) AS TYPE15,
    MAX(CASE WHEN rn = 16 THEN NCC_CTYPE END) AS TYPE16,
    MAX(CASE WHEN rn = 17 THEN NCC_CTYPE END) AS TYPE17,
    MAX(CASE WHEN rn = 18 THEN NCC_CTYPE END) AS TYPE18,
    MAX(CASE WHEN rn = 19 THEN NCC_CTYPE END) AS TYPE19,
    MAX(CASE WHEN rn = 20 THEN NCC_CTYPE END) AS TYPE20
FROM (
    SELECT 
        NCC_CLTID,
        NCC_CTYPE,
        ROW_NUMBER() OVER (
            PARTITION BY NCC_CLTID 
            ORDER BY (SELECT 1) -- 若有指定排序字段（如创建时间）可替换此处保证顺序
        ) AS rn
    FROM Comments
) t
GROUP BY NCC_CLTID;

MySQL5.x适配写法（不支持窗口函数场景） #

SELECT
    NCC_CLTID,
    MAX(CASE WHEN rn = 1 THEN NCC_CTYPE END) AS TYPE1,
    MAX(CASE WHEN rn = 2 THEN NCC_CTYPE END) AS TYPE2,
    -- 后续TYPE3到TYPE20写法和上述通用写法一致，此处省略重复部分
    MAX(CASE WHEN rn = 20 THEN NCC_CTYPE END) AS TYPE20
FROM (
    SELECT 
        NCC_CLTID,
        NCC_CTYPE,
        @rn := IF(@pre_cltid = NCC_CLTID, @rn + 1, 1) AS rn,
        @pre_cltid := NCC_CLTID
    FROM Comments, (SELECT @rn := 0, @pre_cltid := '') AS init
    ORDER BY NCC_CLTID -- 若有其他排序要求可补充排序字段
) t
GROUP BY NCC_CLTID;

内容的提问来源于stack exchange，提问作者Pedro Emilio Vélez