SQL实现：将带编码文本的行转置为列（住院数据场景）

嘿，我完全理解你要做的事——把原来按诊断记录分行的住院数据，转成每个患者的单次住院占一行的宽表，每个诊断对应一个Diagnosis_Code_n列。因为每个住院事件的诊断数量不固定，确实得用动态SQL来处理，下面我给你分数据库类型详细说明解决方案：

一、SQL Server 版本实现 #

步骤1：给每个住院事件的诊断编序号 #

首先我们需要给同一个Patient_ID+Event_ID下的每条诊断记录分配一个序号，这样才能对应到后续的Diagnosis_Code_1、Diagnosis_Code_2等列：

SELECT 
    Patient_ID,
    Event_ID,
    Diagnosis_Code,
    -- 按诊断编码排序，给每个住院事件的诊断编序号
    'Diagnosis_Code_' + CAST(ROW_NUMBER() OVER(PARTITION BY Patient_ID, Event_ID ORDER BY Diagnosis_Code) AS VARCHAR(10)) AS Diagnosis_Col
FROM Inpatients

步骤2：动态生成目标列名 #

接下来要自动获取所有需要的列名（比如Diagnosis_Code_1、Diagnosis_Code_2...），用字符串拼接的方式生成：

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

-- 拼接所有诊断列名，用QUOTENAME避免特殊字符问题
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Diagnosis_Col) 
                      FROM (
                          SELECT DISTINCT 'Diagnosis_Code_' + CAST(ROW_NUMBER() OVER(PARTITION BY Patient_ID, Event_ID ORDER BY Diagnosis_Code) AS VARCHAR(10)) AS Diagnosis_Col
                          FROM Inpatients
                      ) t
                      ORDER BY Diagnosis_Col
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

步骤3：构建并执行动态PIVOT查询 #

最后用PIVOT函数把行转成列，这里用MAX()作为聚合函数（因为每个序号对应唯一的诊断，聚合结果不会失真）：

SET @query = 'SELECT Patient_ID, Event_ID, ' + @cols + ' 
              FROM (
                  SELECT 
                      Patient_ID,
                      Event_ID,
                      Diagnosis_Code,
                      ''Diagnosis_Code_'' + CAST(ROW_NUMBER() OVER(PARTITION BY Patient_ID, Event_ID ORDER BY Diagnosis_Code) AS VARCHAR(10)) AS Diagnosis_Col
                  FROM Inpatients
              ) src
              PIVOT (
                  MAX(Diagnosis_Code)
                  FOR Diagnosis_Col IN (' + @cols + ')
              ) pvt'

-- 执行动态SQL
EXEC sp_executesql @query

二、MySQL 版本实现 #

MySQL没有内置的PIVOT函数，我们用动态生成CASE语句的方式来实现：

步骤1：获取最大诊断数量 #

先确定所有住院事件中最多有多少个诊断，以此确定需要生成多少列：

SELECT MAX(cnt) INTO @max_diag
FROM (
    SELECT COUNT(*) AS cnt
    FROM Inpatients
    GROUP BY Patient_ID, Event_ID
) t;

步骤2：动态生成诊断列的CASE语句 #

SET @sql = NULL;
-- 生成每个诊断列对应的CASE语句
SELECT GROUP_CONCAT(
    'MAX(CASE WHEN rn = ', rn, ' THEN Diagnosis_Code END) AS Diagnosis_Code_', rn
    ORDER BY rn
) INTO @cols
FROM (
    -- 生成从1到@max_diag的序号
    SELECT rn FROM (SELECT @max_diag rn UNION ALL SELECT rn-1 FROM (SELECT @max_diag rn) t WHERE rn > 1) t
) t;

-- 拼接完整的查询语句
SET @sql = CONCAT('
    SELECT Patient_ID, Event_ID, ', @cols, '
    FROM (
        SELECT 
            Patient_ID,
            Event_ID,
            Diagnosis_Code,
            ROW_NUMBER() OVER(PARTITION BY Patient_ID, Event_ID ORDER BY Diagnosis_Code) AS rn
        FROM Inpatients
    ) t
    GROUP BY Patient_ID, Event_ID
');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

额外说明 #

• 上面的代码里，ORDER BY Diagnosis_Code是用来确定诊断列的顺序的，如果你的业务需要按其他顺序（比如诊断记录的录入顺序），可以把排序字段换成对应的列（如果有的话）。
• 没有诊断的列会自动显示为NULL，如果需要显示为空字符串，可以把MAX(Diagnosis_Code)改成ISNULL(MAX(Diagnosis_Code), '')（SQL Server）或者IFNULL(MAX(Diagnosis_Code), '')（MySQL）。

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