在 SQL Server 中生成透视数据

下面是一个不使用PIVOT命令但实现相同结果的解决方案示例。

--demo setup
Declare @Table1 table
    (Department varchar(12), [Round Type] varchar(13), Total int, Passed int)
;

INSERT INTO @Table1
    (Department, [Round Type], Total, Passed)
VALUES
    ('MD', 'Spring', '10', '8'),
    ('MD', 'Winter', '15', '10')
;

--Solution
SELECT department
    ,sum(CASE 
            WHEN [Round Type] = 'Spring'
                THEN Total
            ELSE 0
            END) AS [Spring Total]
    ,sum(CASE 
            WHEN [Round Type] = 'Spring'
                THEN Passed
            ELSE 0
            END) AS [Spring Passed]
    ,sum(CASE 
            WHEN [Round Type] = 'Summer'
                THEN Total
            ELSE 0
            END) AS [Summer Total]
    ,sum(CASE 
            WHEN [Round Type] = 'Summer'
                THEN Passed
            ELSE 0
            END) AS [Summer Passed]
    ,sum(CASE 
            WHEN [Round Type] = 'Fall'
                THEN Total
            ELSE 0
            END) AS [Fall Total]
    ,sum(CASE 
            WHEN [Round Type] = 'Fall'
                THEN Passed
            ELSE 0
            END) AS [Fall Passed]
    ,sum(CASE 
            WHEN [Round Type] = 'Winter'
                THEN Total
            ELSE 0
            END) AS [Winter Total]
    ,sum(CASE 
            WHEN [Round Type] = 'Winter'
                THEN Passed
            ELSE 0
            END) AS [Winter Passed]
FROM @Table1
GROUP BY Department

| department | Spring Total | Spring Passed | Summer Total | Summer Passed | Fall Total | Fall Passed | Winter Total | Winter Passed |
|------------|--------------|---------------|--------------|---------------|------------|-------------|--------------|---------------|
| MD         | 10           | 8             | 0            | 0             | 0          | 0           | 15           | 10            |