如何在SQL、Power BI或Excel中按客户拆分Score列为多列

SQL 实现 #

假设源表名为customer_scores，通过窗口函数生成客户内的Score序号，再用条件聚合完成行转列：

SELECT
    Customer,
    MAX(CASE WHEN rn = 1 THEN Score END) AS Score1,
    MAX(CASE WHEN rn = 2 THEN Score END) AS Score2,
    MAX(CASE WHEN rn = 3 THEN Score END) AS Score3,
    MAX(CASE WHEN rn = 4 THEN Score END) AS Score4
FROM (
    SELECT
        Customer,
        Score,
        ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY (SELECT NULL)) AS rn
    FROM customer_scores
) t
GROUP BY Customer;

说明：ROW_NUMBER()按客户分组生成序号，CASE语句按序号提取对应位置的Score，MAX聚合用于保留有效数值（每个序号对应唯一行，聚合不会丢失数据）。若需按Score排序生成序号，将ORDER BY (SELECT NULL)替换为ORDER BY Score即可。

Power BI 实现 #

方法1：DAX计算表 #

1. 在Power Query中加载源数据
2. 新建计算表，输入以下DAX公式：

PivotedScores = 
VAR RankedScores = 
    ADDCOLUMNS(
        'customer_scores',
        "Rank", RANKX(FILTER('customer_scores', 'customer_scores'[Customer] = EARLIER('customer_scores'[Customer])), 'customer_scores'[Score], , ASC, Dense)
    )
RETURN
    SUMMARIZECOLUMNS(
        RankedScores[Customer],
        "Score1", CALCULATE(MAX(RankedScores[Score]), RankedScores[Rank] = 1),
        "Score2", CALCULATE(MAX(RankedScores[Score]), RankedScores[Rank] = 2),
        "Score3", CALCULATE(MAX(RankedScores[Score]), RankedScores[Rank] = 3),
        "Score4", CALCULATE(MAX(RankedScores[Score]), RankedScores[Rank] = 4)
    )

方法2：Power Query透视列 #

1. 进入Power Query编辑器，选中源表
2. 添加索引列：点击添加列→索引列→从1开始
3. 按Customer分组：点击转换→分组依据，分组列选Customer，新列名设为Scores，操作选所有行
4. 展开Scores列，仅保留Score和索引列
5. 点击转换→透视列，值列选Score，手动调整列名为Score1、Score2等
6. 关闭并应用，即可得到目标表

Excel 实现 #

方法1：透视表+辅助列 #

1. 给源数据添加序号辅助列：在C2输入=COUNTIF($A$2:A2,A2)，下拉填充，得到每个客户的Score序号
2. 选中A1:C8（源数据+辅助列），插入透视表
3. 透视表字段设置：Customer拖到行，序号拖到列，Score拖到值，值字段选择最大值
4. 手动调整列名为Score1、Score2等，空值保留即可

方法2：动态数组公式（Excel 365/2021） #

在空白单元格（如E2）输入公式，回车后自动生成目标表格：

=LET(
    custs, UNIQUE(A2:A8),
    max_scores, MAX(COUNTIF(A2:A8, custs)),
    headers, HSTACK("Customer", "Score"&SEQUENCE(max_scores)),
    scores, BYROW(custs, LAMBDA(c, HSTACK(c, TAKE(FILTER(B2:B8, A2:A8=c), max_scores)))),
    VSTACK(headers, scores)
)

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