将CSV数据导入MySQL：合并多行数据至单条记录多字段

MySQL无法在导入过程中直接完成这种行转列的转换，必须先将原始数据导入临时表，再通过SQL查询生成目标结构的表。以下是具体操作步骤：

1. 创建原始数据临时表 #

先建立一个与CSV结构完全匹配的临时表，用于存储导入的原始数据：

CREATE TABLE patient_conditions_raw (
    practice VARCHAR(50),
    provider VARCHAR(50),
    mrn VARCHAR(20) NOT NULL,
    patient VARCHAR(50),
    condition VARCHAR(100),
    last_visit DATE
);

2. 导入CSV文件 #

使用LOAD DATA INFILE命令批量导入CSV（如果习惯图形界面工具，也可以通过phpMyAdmin等工具的导入功能完成）：

LOAD DATA INFILE '/path/to/your/patient_data.csv'
INTO TABLE patient_conditions_raw
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 忽略CSV的表头行

3. 创建目标表并转换数据 #

首先创建符合需求的目标表，包含condition1至condition6字段：

CREATE TABLE patient_conditions_pivoted (
    practice VARCHAR(50),
    provider VARCHAR(50),
    mrn VARCHAR(20) NOT NULL PRIMARY KEY,
    patient VARCHAR(50),
    condition1 VARCHAR(100),
    condition2 VARCHAR(100),
    condition3 VARCHAR(100),
    condition4 VARCHAR(100),
    condition5 VARCHAR(100),
    condition6 VARCHAR(100),
    last_visit DATE
);

然后通过嵌套查询将临时表的行数据转换为列数据，插入目标表：

INSERT INTO patient_conditions_pivoted
SELECT 
    practice,
    provider,
    mrn,
    patient,
    MAX(CASE WHEN cond_num = 1 THEN condition END) AS condition1,
    MAX(CASE WHEN cond_num = 2 THEN condition END) AS condition2,
    MAX(CASE WHEN cond_num = 3 THEN condition END) AS condition3,
    MAX(CASE WHEN cond_num = 4 THEN condition END) AS condition4,
    MAX(CASE WHEN cond_num = 5 THEN condition END) AS condition5,
    MAX(CASE WHEN cond_num = 6 THEN condition END) AS condition6,
    last_visit
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY mrn ORDER BY condition) AS cond_num
    FROM patient_conditions_raw
) AS numbered_conditions
GROUP BY mrn, practice, provider, patient, last_visit;

关键逻辑说明： #

• 子查询通过ROW_NUMBER() OVER (PARTITION BY mrn ORDER BY condition)为每个病历号（MRN）下的诊断结果分配序号（1到最多6）。
• 外层查询用MAX(CASE ...)将不同序号的诊断结果分别映射到condition1至condition6字段。
• 同一MRN对应的practice、provider等字段值一致，GROUP BY时包含这些字段可保证数据准确性。

完成转换后，若不需要临时表可删除：

DROP TABLE patient_conditions_raw;

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