SQL Server 2008子查询结果如何合并为单行？

Hey there! Let's fix that query to get your desired single-row output. The issue with your current code is two-fold: you're grouping by loan_type (which splits results into separate rows), and your CASE statements only populate one column per group. Here's how to adjust it:

Fixed Query #

SELECT 
    'RCO' AS Stg_nm,
    SUM(CASE WHEN sq2.loan_type = 'Consumer loan' THEN sq2.user_count ELSE 0 END) AS Cons_ln,
    SUM(CASE WHEN sq2.loan_type = 'Auto loan' THEN sq2.user_count ELSE 0 END) AS Auto_lan,
    SUM(CASE WHEN sq2.loan_type = 'Credit Card' THEN sq2.user_count ELSE 0 END) AS Credit_card
FROM (
    SELECT 
        'RC0' AS ws_name, 
        'Consumer loan' AS loan_type, 
        COUNT(DISTINCT a.bpm_referenceno) AS user_count, 
        a.takenby AS user_id 
    FROM BM_RLOS_DecisionHistoryForm a 
    INNER JOIN (
        SELECT m.bpm_referenceno 
        FROM BM_RLOS_EXTTABLE m 
        WHERE m.loan_type = 'Consumer Loan'
    ) sq1 ON a.bpm_referenceno = sq1.bpm_referenceno 
    WHERE a.winame='RCO' 
    GROUP BY a.takenby 

    UNION 

    SELECT 
        'RC0','Auto loan', 
        COUNT(DISTINCT a.bpm_referenceno), 
        a.takenby 
    FROM BM_RLOS_DecisionHistoryForm a 
    INNER JOIN (
        SELECT m.bpm_referenceno 
        FROM BM_RLOS_EXTTABLE m 
        WHERE m.loan_type='Auto Loan'
    )sq1 ON a.bpm_referenceno = sq1.bpm_referenceno 
    WHERE a.winame='RCO' 
    GROUP BY a.takenby 

    UNION 

    SELECT 
        'RC0','Credit Card', 
        COUNT(DISTINCT a.bpm_referenceno), 
        a.takenby 
    FROM BM_RLOS_DecisionHistoryForm a 
    INNER JOIN (
        SELECT m.bpm_referenceno 
        FROM BM_RLOS_EXTTABLE m 
        WHERE m.loan_type='Credit Card'
    )sq1 ON a.bpm_referenceno = sq1.bpm_referenceno 
    WHERE a.winame='RCO' 
    GROUP BY a.takenby
) sq2 
GROUP BY sq2.ws_name

Key Changes Explained #

1. Removed sq2.loan_type from GROUP BY: Now the entire result set groups only by your stage name (ws_name), so you'll get one row per stage instead of one row per loan type.
2. Wrapped SUM around CASE statements: Instead of conditionally summing per group, we now sum the values only where the loan type matches each column. Non-matching rows contribute 0 to the sum, giving you the total count for each loan type in a single row.

Bonus: Optimized Version (Less Repetition) #

You can also simplify the query to avoid repeating the same JOIN logic three times. This version does the same job but is cleaner and more efficient:

SELECT 
    'RCO' AS Stg_nm,
    SUM(CASE WHEN m.loan_type = 'Consumer Loan' THEN user_count ELSE 0 END) AS Cons_ln,
    SUM(CASE WHEN m.loan_type = 'Auto Loan' THEN user_count ELSE 0 END) AS Auto_lan,
    SUM(CASE WHEN m.loan_type = 'Credit Card' THEN user_count ELSE 0 END) AS Credit_card
FROM (
    SELECT 
        a.takenby,
        m.loan_type,
        COUNT(DISTINCT a.bpm_referenceno) AS user_count
    FROM BM_RLOS_DecisionHistoryForm a 
    INNER JOIN BM_RLOS_EXTTABLE m ON a.bpm_referenceno = m.bpm_referenceno
    WHERE a.winame='RCO' 
      AND m.loan_type IN ('Consumer Loan', 'Auto Loan', 'Credit Card')
    GROUP BY a.takenby, m.loan_type
) sq2

Either version will output exactly what you want:

内容的提问来源于stack exchange，提问作者Md Kamran Azam