SQL Server 2012多列垂直转水平：临时表#temp2行转列需求

Here's a solution using SQL Server's UNPIVOT and PIVOT functions that will give you exactly the output you need. The approach first converts your two measure columns (Outstanding and Members) into rows, then pivots the DaysDelay values into columns, resulting in one row per Loan Officer per measure.

SELECT 
    [Loan Officer],
    [Days 0], 
    [Days 1-30], 
    [Days 31-60], 
    [Days 61-90], 
    [Days 91-180], 
    [Days over 180]
FROM (
    -- Step 1: Unpivot the two measure columns into rows with a type indicator
    SELECT 
        [Loan Officer],
        DaysDelay,
        Value,
        RowType
    FROM #temp2
    UNPIVOT (
        Value FOR RowType IN (Outstanding, Members)
    ) AS UnpivotData
) AS Source
-- Step 2: Pivot the DaysDelay categories into columns
PIVOT (
    SUM(Value) -- Safe here since each (Loan Officer, RowType, DaysDelay) has one value
    FOR DaysDelay IN (
        [Days 0], 
        [Days 1-30], 
        [Days 31-60], 
        [Days 61-90], 
        [Days 91-180], 
        [Days over 180]
    )
) AS PivotedResult
-- Order to keep Outstanding rows above Members rows for each officer
ORDER BY 
    [Loan Officer],
    CASE RowType WHEN 'Outstanding' THEN 1 ELSE 2 END;

How this works: #

1. UNPIVOT: Takes each row in #temp2 and creates two rows—one for the Outstanding value and one for the Members value. This gives us a flat list of all values with their corresponding Loan Officer, DaysDelay, and measure type.
2. PIVOT: Groups the data by Loan Officer and measure type, then spreads the DaysDelay values into separate columns. Missing values (like Kevin's Days 61-90) will automatically show as NULL, matching your expected output.
3. ORDER BY: Ensures that for each Loan Officer, the Outstanding row comes before the Members row.

This solution is compatible with SQL Server 2012 and produces exactly the structure you requested.

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