如何用Dynamic SQL+条件PIVOT实现垂直数据集转水平数据集？

需要将垂直结构的数据集转换为水平结构：

• 数据集里唯一id（VARCHAR类型）最多出现13次，每行包含非空的id和c_id，以及op、score、sp_id、p等字段
• op值按id求和必须为100，用于数据准确性校验
• 转换后每个唯一id对应一行：
  • 当op为NULL或0时，不为该c_id生成新列，但要捕获score
  • 若sp_id为NULL，对应的spid_score也为NULL
• 由于id关联的c_id数量会随时间变化，改用Dynamic SQL，但不知道如何生成带_1、_2这类序号后缀的动态列，寻求解决方案

示例数据定义 #

DECLARE @Table1 TABLE
(
  id VARCHAR(1) NOT NULL
 ,c_id INT NOT NULL
 ,op FLOAT NULL
 ,score FLOAT NULL
 ,sp_id INT NULL     
 ,p VARCHAR(2) NULL
) 
INSERT INTO @Table1 
(id, c_id, op, score, sp_id, p)
VALUES ('1', '1', '51', '4','2','a')
      ,('1', '2', NULL, '3','1',NULL)
      ,('1', '3','20', '4',NULL,'a')
      ,('1', '4', '20', '4','5','a')
      ,('1', '5', '9', '4','4','a')
      ,('2', '6','100', NULL, NULL,NULL)
      ,('3', '7','100','4',NULL,'a')
;

数据展示 #

目标结构示例 #

DECLARE @Table2 TABLE
(  p VARCHAR (2) NULL
  ,sum_op INT NULL
  ,id VARCHAR(1) NOT NULL         
  ,cid1 INT NULL
  ,op1 FLOAT NULL
  ,cid1_score FLOAT NULL
  ,spid1 INT NULL
  ,spid1_score FLOAT NULL       
  ,cid2 INT NULL
  ,op2 FLOAT NULL
  ,cid2_score FLOAT NULL
  ,spid2 INT NULL   
  ,spid2_score FLOAT NULL     
  ,cid3 INT NULL
  ,op3 FLOAT NULL
  ,cid3_score FLOAT NULL
  ,spid3 INT NULL   
  ,spid3_score FLOAT NULL     
  ,cid4 INT NULL
  ,op4 FLOAT NULL
  ,cid4_score FLOAT NULL
  ,spid4 INT NULL
  ,spid4_score FLOAT NULL
) 

INSERT INTO @Table2
(  p,sum_op ,id,cid1,op1,cid1_score,spid1,spid1_score,cid2,op2,cid2_score,spid2,spid2_score,cid3,op3,cid3_score,spid3,spid3_score,cid4,op4,cid4_score,spid4,spid4_score)
VALUES ('a','100','1','1','51','4','2','3','3','20','4',NULL,NULL,'4','20','4','5',NULL,'5','9','4','4',NULL),
       (NULL,'100','2','6','100', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
       ('a','100','3','7','100','4',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
;

已尝试的动态SQL代码 #

DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 生成基于cid值的动态列列表，不知道如何添加下划线和迭代序号
SELECT @columns = COALESCE(@columns + ', ', '') + CAST(cid AS VARCHAR)
FROM (
  SELECT DISTINCT cid
  FROM table1
) AS Contacts;

-- 构建动态SQL语句
SET @sql = N'
SELECT id, ' + @columns + ', op, score, spid
FROM (
  SELECT id, 
         CAST(ROW_NUMBER() OVER (PARTITION BY id ORDER BY contact_id) AS VARCHAR) AS cid_column,
         cid, op, score, spid
  FROM table1
) AS SourceTable
PIVOT (
  MAX(cid)
  FOR cid IN (' + @columns + ')
) AS PivotTable
ORDER BY id;
';

-- 执行动态SQL语句
EXEC sp_executesql @sql;

解决方案 #

要生成带_1、_2序号后缀的动态列，核心是先为每个id下的c_id分配连续序号，再基于序号生成对应列名。以下是完整实现代码：

DECLARE @maxSeq INT;
DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 获取每个id下的最大序号
SELECT @maxSeq = MAX(seq)
FROM (
    SELECT id, ROW_NUMBER() OVER(PARTITION BY id ORDER BY c_id) AS seq
    FROM @Table1
) t;

-- 生成动态列定义和聚合逻辑
WITH seqCTE AS (
    SELECT 1 AS seq
    UNION ALL
    SELECT seq + 1 FROM seqCTE WHERE seq < @maxSeq
)
SELECT @cols = COALESCE(@cols + ',', '') + 
    CONCAT(
        'MAX(CASE WHEN seq = ', seq, ' THEN c_id END) AS cid', seq, ',',
        'MAX(CASE WHEN seq = ', seq, ' THEN op END) AS op', seq, ',',
        'MAX(CASE WHEN seq = ', seq, ' THEN score END) AS cid', seq, '_score,',
        'MAX(CASE WHEN seq = ', seq, ' THEN sp_id END) AS spid', seq, ',',
        'MAX(CASE WHEN seq = ', seq, ' THEN (SELECT score FROM @Table1 t2 WHERE t2.id = t.id AND t2.sp_id = t.sp_id) END) AS spid', seq, '_score'
    )
FROM seqCTE;

-- 构建完整动态SQL
SET @sql = CONCAT(N'
SELECT 
    MAX(p) AS p,
    SUM(op) AS sum_op,
    id,
    ', @cols, '
FROM (
    SELECT 
        id,
        c_id,
        op,
        score,
        sp_id,
        p,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY c_id) AS seq
    FROM @Table1
) t
GROUP BY id
ORDER BY id;
');

-- 执行动态SQL
EXEC sp_executesql @sql;

代码说明 #

1. 序号分配：通过ROW_NUMBER() OVER(PARTITION BY id ORDER BY c_id)为每个id下的c_id分配连续序号seq
2. 动态列生成：借助递归CTE生成从1到最大序号的序列，为每个序号生成对应的cidN、opN、cidN_score、spidN、spidN_score列
3. 数据聚合：使用MAX(CASE...)实现垂直转水平的结构转换，同时计算sum_op完成数据校验，聚合p字段
4. spid_score处理：通过子查询匹配sp_id对应的score，确保sp_id为NULL时对应字段也为NULL

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