使用Azure Data Factory动态生成分组序号式新列的技术问题

要完成这种长表转宽表的需求，核心是先给每个分组内的行添加序号，再通过条件聚合或动态SQL实现列的动态生成，以下是具体步骤：

1. 生成组内序号 #

先通过窗口函数ROW_NUMBER()，按id和group分组，给每组内的行分配序号（序号顺序可根据需求调整，示例按stock降序排列）：

SELECT 
    id,
    `group`, -- group是SQL关键字，需用反引号转义
    stock,
    stockplace,
    ROW_NUMBER() OVER (PARTITION BY id, `group` ORDER BY stock DESC) AS rn
FROM your_table;

执行后得到中间结果：

id, group, stock, stockplace, rn
1, A, 30, X, 1
1, A, 20, Y, 2
1, A, 10, Z, 3
1, B, 10, AAA, 1
1, B, 5, EEE, 2

2. 静态条件聚合转宽表 #

如果分组和每组的行数固定，可以直接用MAX(CASE...)做条件聚合，把行转成列：

SELECT 
    id,
    -- 处理A组的列
    MAX(CASE WHEN `group` = 'A' AND rn = 1 THEN stock END) AS stockA1,
    MAX(CASE WHEN `group` = 'A' AND rn = 1 THEN stockplace END) AS stockplaceA1,
    MAX(CASE WHEN `group` = 'A' AND rn = 2 THEN stock END) AS stockA2,
    MAX(CASE WHEN `group` = 'A' AND rn = 2 THEN stockplace END) AS stockplaceA2,
    MAX(CASE WHEN `group` = 'A' AND rn = 3 THEN stock END) AS stockA3,
    MAX(CASE WHEN `group` = 'A' AND rn = 3 THEN stockplace END) AS stockplaceA3,
    -- 处理B组的列
    MAX(CASE WHEN `group` = 'B' AND rn = 1 THEN stock END) AS stockB1,
    MAX(CASE WHEN `group` = 'B' AND rn = 1 THEN stockplace END) AS stockplaceB1,
    MAX(CASE WHEN `group` = 'B' AND rn = 2 THEN stock END) AS stockB2,
    MAX(CASE WHEN `group` = 'B' AND rn = 2 THEN stockplace END) AS stockplaceB2
FROM (
    SELECT 
        id,
        `group`,
        stock,
        stockplace,
        ROW_NUMBER() OVER (PARTITION BY id, `group` ORDER BY stock DESC) AS rn
    FROM your_table
) t
GROUP BY id;

执行后就能得到你需要的目标结果。

3. 动态SQL实现通用转宽表 #

如果分组（如A、B）或每组的行数不固定，需要用动态SQL自动生成列：
以MySQL为例，代码如下：

-- 拼接动态SQL语句
SET @sql = NULL;
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'MAX(CASE WHEN `group` = ''', `group`, ''' AND rn = ', rn, ' THEN stock END) AS stock', `group`, rn,
        ', MAX(CASE WHEN `group` = ''', `group`, ''' AND rn = ', rn, ' THEN stockplace END) AS stockplace', `group`, rn
    )
) INTO @sql
FROM (
    SELECT `group`, ROW_NUMBER() OVER (PARTITION BY id, `group` ORDER BY stock DESC) AS rn
    FROM your_table
) t;

-- 组装完整SQL并执行
SET @sql = CONCAT('SELECT id, ', @sql, ' FROM (SELECT id, `group`, stock, stockplace, ROW_NUMBER() OVER (PARTITION BY id, `group` ORDER BY stock DESC) AS rn FROM your_table) t GROUP BY id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这段代码会自动识别所有分组和每组的行数，动态生成对应的列，适配不同的数据结构。

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