SQL：如何从单列拆分生成多命名列？条件筛选实现求助

你需要用条件判断+聚合的方式实现列拆分，你之前的SQL写法不符合SELECT语句的语法规范，以下是可行的实现方案：

方法1：单条记录匹配（适用于每个(sample, specimen_id)分组下各前缀仅一条符合条件的记录） #

SELECT 
    sample,
    specimen_id,
    -- 匹配amp1前缀且read≥100的记录
    CASE WHEN amp ILIKE 'amp1%' AND read >= 100 THEN amp ELSE NULL END AS amp1,
    CASE WHEN amp ILIKE 'amp2%' AND read >= 100 THEN amp ELSE NULL END AS amp2,
    CASE WHEN amp ILIKE 'amp3%' AND read >= 100 THEN amp ELSE NULL END AS amp3,
    CASE WHEN amp ILIKE 'amp4%' AND read >= 100 THEN amp ELSE NULL END AS amp4,
    CASE WHEN amp ILIKE 'amp5%' AND read >= 100 THEN amp ELSE NULL END AS amp5
FROM schema.table;

方法2：多记录聚合（适用于每个分组下各前缀可能有多条符合条件的记录） #

如果同一个(sample, specimen_id)下存在多条匹配同一前缀的记录，用聚合函数可以将有效值合并到单行，避免结果出现冗余行：

SELECT 
    sample,
    specimen_id,
    MAX(CASE WHEN amp ILIKE 'amp1%' AND read >= 100 THEN amp ELSE NULL END) AS amp1,
    MAX(CASE WHEN amp ILIKE 'amp2%' AND read >= 100 THEN amp ELSE NULL END) AS amp2,
    MAX(CASE WHEN amp ILIKE 'amp3%' AND read >= 100 THEN amp ELSE NULL END) AS amp3,
    MAX(CASE WHEN amp ILIKE 'amp4%' AND read >= 100 THEN amp ELSE NULL END) AS amp4,
    MAX(CASE WHEN amp ILIKE 'amp5%' AND read >= 100 THEN amp ELSE NULL END) AS amp5
FROM schema.table
GROUP BY sample, specimen_id;

补充说明 #

• 两种写法的核心都是通过CASE WHEN判断记录是否满足前缀匹配和read值条件，将符合要求的amp值映射到对应新列，不符合的填充NULL。
• 若你需要提取前缀后的基因名称而非完整amp值，可使用字符串截取函数，比如SUBSTRING(amp FROM 4)（针对amp1xxx这类前缀长度为4的格式）。

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