医疗索赔数据按Claim Number聚合提取唯一EX字段值的SQL需求

Hey Chris, I’ve got a clean, scalable SQL solution for your medical claim data problem! The key here is to first "unpivot" your EX1-EX6 columns into rows, remove duplicates, then aggregate the unique codes into a single comma-separated string. This approach works across most major SQL databases and avoids the messy subquery stitching you were worried about.

Core Approach Breakdown #

1. Unpivot Columns: Turn your 6 EX columns into a single column of code values, one per row, linked to their Claim Number.
2. Remove Duplicates: Ensure each code only appears once per Claim Number.
3. Aggregate into String: Combine the unique codes into a single comma-separated string for each claim.

Solution for MySQL (8.0+) #

MySQL's GROUP_CONCAT supports DISTINCT directly, making this straightforward:

SELECT 
    `Claim Number`,
    GROUP_CONCAT(DISTINCT code ORDER BY code SEPARATOR ',') AS Codes
FROM (
    -- Unpivot all EX columns into rows
    SELECT `Claim Number`, EX1 AS code FROM your_claims_table
    UNION ALL
    SELECT `Claim Number`, EX2 AS code FROM your_claims_table
    UNION ALL
    SELECT `Claim Number`, EX3 AS code FROM your_claims_table
    UNION ALL
    SELECT `Claim Number`, EX4 AS code FROM your_claims_table
    UNION ALL
    SELECT `Claim Number`, EX5 AS code FROM your_claims_table
    UNION ALL
    SELECT `Claim Number`, EX6 AS code FROM your_claims_table
) AS unpivoted_codes
WHERE code IS NOT NULL -- Skip any empty code values
GROUP BY `Claim Number`;

Solution for SQL Server (2017+) #

Use STRING_AGG with DISTINCT (available in 2017 and later):

SELECT 
    [Claim Number],
    STRING_AGG(DISTINCT code, ',') WITHIN GROUP (ORDER BY code) AS Codes
FROM (
    SELECT [Claim Number], EX1 AS code FROM your_claims_table
    UNION ALL
    SELECT [Claim Number], EX2 AS code FROM your_claims_table
    UNION ALL
    SELECT [Claim Number], EX3 AS code FROM your_claims_table
    UNION ALL
    SELECT [Claim Number], EX4 AS code FROM your_claims_table
    UNION ALL
    SELECT [Claim Number], EX5 AS code FROM your_claims_table
    UNION ALL
    SELECT [Claim Number], EX6 AS code FROM your_claims_table
) AS unpivoted_codes
WHERE code IS NOT NULL
GROUP BY [Claim Number];

Solution for PostgreSQL #

PostgreSQL's STRING_AGG also supports DISTINCT:

SELECT 
    "Claim Number",
    STRING_AGG(DISTINCT code, ',' ORDER BY code) AS Codes
FROM (
    SELECT "Claim Number", EX1 AS code FROM your_claims_table
    UNION ALL
    SELECT "Claim Number", EX2 AS code FROM your_claims_table
    UNION ALL
    SELECT "Claim Number", EX3 AS code FROM your_claims_table
    UNION ALL
    SELECT "Claim Number", EX4 AS code FROM your_claims_table
    UNION ALL
    SELECT "Claim Number", EX5 AS code FROM your_claims_table
    UNION ALL
    SELECT "Claim Number", EX6 AS code FROM your_claims_table
) AS unpivoted_codes
WHERE code IS NOT NULL
GROUP BY "Claim Number";

For Older MySQL Versions (Pre-8.0) #

If you're stuck on MySQL 5.x which doesn't support GROUP_CONCAT(DISTINCT), add an extra deduplication step:

SELECT 
    `Claim Number`,
    GROUP_CONCAT(code ORDER BY code SEPARATOR ',') AS Codes
FROM (
    -- Deduplicate codes per claim first
    SELECT DISTINCT `Claim Number`, code
    FROM (
        SELECT `Claim Number`, EX1 AS code FROM your_claims_table
        UNION ALL
        SELECT `Claim Number`, EX2 AS code FROM your_claims_table
        UNION ALL
        SELECT `Claim Number`, EX3 AS code FROM your_claims_table
        UNION ALL
        SELECT `Claim Number`, EX4 AS code FROM your_claims_table
        UNION ALL
        SELECT `Claim Number`, EX5 AS code FROM your_claims_table
        UNION ALL
        SELECT `Claim Number`, EX6 AS code FROM your_claims_table
    ) AS unpivoted_codes
    WHERE code IS NOT NULL
) AS deduplicated_codes
GROUP BY `Claim Number`;

Why This Works Better #

• Scalable: Handles any number of rows per claim (from 1 to 75+) without changing the code.
• Clean Logic: Unpivoting first makes deduplication and aggregation simple, no messy nested subqueries.
• Orderly: The ORDER BY in the aggregation function ensures your codes are consistently ordered, making results easier to read.

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