MSSQL 2012无聚合多列数据表转置技术求助

Hey Peter, I get it—trying to pivot/unpivot data when you're not a full-time dev can feel like navigating a maze. Let's break this down simply: since each ClinicalTrialID has 14 repeating sets of Mode_Antibiotics, ModeQualifier, and ModeMIC, we need to unpivot those wide columns into a narrow row-based format.

For SQL Server 2012, the most flexible approach here is using CROSS APPLY with a VALUES clause. This lets us group each trio of fields (one from each of your 14 sets) into individual rows, without the limitations of the basic UNPIVOT operator.

Example SQL Code #

Assuming your source table is named ClinicalTrialData with columns like ClinicalTrialID, Mode_Antibiotics_1, ModeQualifier_1, ModeMIC_1, all the way up to _14, here's the query you can use:

SELECT
    ct.ClinicalTrialID,
    ca.SetNumber,
    ca.Mode_Antibiotics,
    ca.ModeQualifier,
    ca.ModeMIC
FROM
    ClinicalTrialData ct
CROSS APPLY (
    VALUES
        (1, ct.Mode_Antibiotics_1, ct.ModeQualifier_1, ct.ModeMIC_1),
        (2, ct.Mode_Antibiotics_2, ct.ModeQualifier_2, ct.ModeMIC_2),
        (3, ct.Mode_Antibiotics_3, ct.ModeQualifier_3, ct.ModeMIC_3),
        (4, ct.Mode_Antibiotics_4, ct.ModeQualifier_4, ct.ModeMIC_4),
        (5, ct.Mode_Antibiotics_5, ct.ModeQualifier_5, ct.ModeMIC_5),
        (6, ct.Mode_Antibiotics_6, ct.ModeQualifier_6, ct.ModeMIC_6),
        (7, ct.Mode_Antibiotics_7, ct.ModeQualifier_7, ct.ModeMIC_7),
        (8, ct.Mode_Antibiotics_8, ct.ModeQualifier_8, ct.ModeMIC_8),
        (9, ct.Mode_Antibiotics_9, ct.ModeQualifier_9, ct.ModeMIC_9),
        (10, ct.Mode_Antibiotics_10, ct.ModeQualifier_10, ct.ModeMIC_10),
        (11, ct.Mode_Antibiotics_11, ct.ModeQualifier_11, ct.ModeMIC_11),
        (12, ct.Mode_Antibiotics_12, ct.ModeQualifier_12, ct.ModeMIC_12),
        (13, ct.Mode_Antibiotics_13, ct.ModeQualifier_13, ct.ModeMIC_13),
        (14, ct.Mode_Antibiotics_14, ct.ModeQualifier_14, ct.ModeMIC_14)
) ca (SetNumber, Mode_Antibiotics, ModeQualifier, ModeMIC)
-- Optional: Filter out rows where all three fields are NULL (if you don't want empty sets)
WHERE
    ca.Mode_Antibiotics IS NOT NULL
    OR ca.ModeQualifier IS NOT NULL
    OR ca.ModeMIC IS NOT NULL;

Key Details to Note #

• SetNumber: This column tracks which of the 14 original sets each row comes from—useful if you need to reference the original position later.
• Handling NULLs: The WHERE clause at the end removes rows where all three fields are empty. If you want to keep those empty sets (e.g., to document that a trial had no data for set 5), just remove that clause.
• Column Naming: If your columns don't use the _1 to _14 suffix (e.g., Mode_Antibiotics1 instead of Mode_Antibiotics_1), adjust the field names in the VALUES list to match your actual schema.

This approach should efficiently handle your ~3000 ClinicalTrialIDs, generating 3000 * 14 = 42,000 rows (minus any filtered NULL sets) in the target format you need.

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