按序将行转置为指定通用列：SQL种族数据转置方案问询

你的思路方向是对的，但之前的PIVOT用错了维度——你应该基于每个salesforce_id下种族的顺序序号来转置，而不是种族名称本身。这样就能得到你想要的race_1到race_5通用列，同时性能也会比多次左连接好很多。

具体实现代码 #

首先，我们先给每个用户的种族分配专属的顺序序号（而非全局行号），然后基于这个序号做PIVOT：

WITH SplitEthnicities AS (
    SELECT 
        salesforce_id,
        value AS race,
        -- 核心：按用户分组，给每个用户的种族分配顺序序号
        ROW_NUMBER() OVER (PARTITION BY salesforce_id ORDER BY (SELECT NULL)) AS race_num
    FROM JRM_EXPORT_CONTACT
    CROSS APPLY STRING_SPLIT(ipeds_ethnicities, ';')
    -- 过滤拆分出来的空值（避免原字符串末尾分号导致的空行）
    WHERE TRIM(value) <> ''
)
SELECT 
    salesforce_id,
    -- 将序号1-5映射为race_1到race_5
    [1] AS race_1,
    [2] AS race_2,
    [3] AS race_3,
    [4] AS race_4,
    [5] AS race_5
FROM SplitEthnicities
PIVOT (
    MAX(race)
    FOR race_num IN ([1], [2], [3], [4], [5])
) AS PivotTable;

关键细节说明 #

1. 用户专属序号：PARTITION BY salesforce_id确保序号是每个用户独立计数的，比如用户A的第一个种族是race_1，用户B的第一个种族也是race_1，不会互相干扰。
2. 顺序一致性：如果你的SQL Server版本是2022及以上，推荐使用STRING_SPLIT的enable_ordinal参数来保证拆分顺序和原字符串一致，修改后的拆分部分如下：

   CROSS APPLY STRING_SPLIT(ipeds_ethnicities, ';', 1) AS split_result
   

   然后把ORDER BY (SELECT NULL)改成ORDER BY split_result.ordinal，这样就能严格按照原字符串中分号的顺序来生成race_1到race_5。
3. 性能优势：这个方法只需要对原表做一次扫描+拆分，然后一次PIVOT操作，比多次左连接的嵌套查询效率高得多，尤其当数据量较大时差异会很明显。

为什么之前的方法不行？ #

你之前的PIVOT是基于种族名称（比如[African American]）来生成列，所以列名是固定的种族值；而我们需要的是按每个用户的种族出现顺序生成通用列，因此必须先给每个用户的种族分配顺序序号，再基于序号做转置。

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