SQL Server多条件合并行并拼接血压数据的查询实现需求

Got it, let's fix this SQL query to meet your exact requirements for SQL Server 2014. Here's a solution that handles grouping, blood pressure aggregation, NULL handling, and preserves BMI records as-is:

WITH BloodPressureAgg AS (
    -- First, aggregate max Systole and Diastole per group
    SELECT
        Service,
        ID,
        Date,
        State,
        MAX(CASE WHEN Name = 'Systole' THEN Results END) AS MaxSystole,
        MAX(CASE WHEN Name = 'Diastole' THEN Results END) AS MaxDiastole
    FROM mytable
    WHERE Name IN ('Systole', 'Diastole')
    GROUP BY Service, ID, Date, State
),
BloodPressureRows AS (
    -- Generate combined Blood Pressure row when both values exist
    SELECT
        Service,
        ID,
        Date,
        State,
        'Blood Pressure' AS Name,
        CONCAT(MaxSystole, '/', MaxDiastole) AS Results
    FROM BloodPressureAgg
    WHERE MaxSystole IS NOT NULL AND MaxDiastole IS NOT NULL

    UNION ALL

    -- Keep standalone Systole row if only Systole exists
    SELECT
        Service,
        ID,
        Date,
        State,
        'Systole' AS Name,
        MaxSystole AS Results
    FROM BloodPressureAgg
    WHERE MaxSystole IS NOT NULL AND MaxDiastole IS NULL

    UNION ALL

    -- Keep standalone Diastole row if only Diastole exists
    SELECT
        Service,
        ID,
        Date,
        State,
        'Diastole' AS Name,
        MaxDiastole AS Results
    FROM BloodPressureAgg
    WHERE MaxSystole IS NULL AND MaxDiastole IS NOT NULL
)
-- Combine processed blood pressure rows with original BMI records
SELECT Service, ID, Date, State, Name, Results
FROM BloodPressureRows

UNION ALL

SELECT Service, ID, Date, State, Name, Results
FROM mytable
WHERE Name = 'BMI'
-- Optional: Order the output to match your example
ORDER BY Service, ID, Date, State, Name;

How this works: #

1. BloodPressureAgg CTE: Groups your data by Service, ID, Date, and State, then calculates the maximum value for Systole and Diastole in each group. This handles the requirement to use the highest values for blood pressure.
2. BloodPressureRows CTE: Creates the correct output rows for blood pressure:
   • If both Systole and Diastole have values, it combines them into a single Blood Pressure row with the format max_systole/max_diastole.
   • If only one of the values exists (or is non-NULL), it keeps the original name (Systole/Diastole) and the single value, matching your requirement for preserving missing/null states.
3. Final Union: Merges the processed blood pressure rows with the original BMI records (since those don't need aggregation) and sorts the output to match your example.

Why your original query didn't work: #

Your initial attempt was just renaming rows without aggregating the maximum values or handling the conditional logic for when to combine vs. keep standalone blood pressure entries. This solution addresses both the grouping/aggregation and the NULL/missing value scenarios.

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