tsql - TSQL Pivot - 如此简单却让我大吃一惊

Question

我一生都无法弄清楚为什么我的枢轴没有将基本数据分组到一行……我做错了什么？我尝试通过不对源数据进行分组、对所有源数据进行分组来重写查询……是因为枢轴值是字符串（varchar）值吗？

这太尴尬了……

Create Table #temp 
(
endYear int, 
schoolLevelDescription varchar(25), 
staffCt int, 
staffADA decimal(5,1), 
trend varchar(10), 
srt int
)

Insert Into #temp Select 2018, 'Elementary', 2729, 93.2, 'red', 1
Insert Into #temp Select 2018, 'Combo', 169, 91.3, 'green', 4
Insert Into #temp Select 2018, 'High', 1467, 94.6, 'red', 3
Insert Into #temp Select 2019, 'Elementary', 61, 94.8, null, 1
Insert Into #temp Select 2018, 'Middle', 1027, 94.5, 'red', 2

Select  schoolLevelDescription,
        [2018]              y1, 
        IsNull([2019], '-') y2, 
        IsNull([2020], '-') y3, 
        IsNull([2021], '-') y4,
        trend 
From    (
        Select  endYear,
                schoolLevelDescription,  
                max(cast(staffCt as varchar(5)) + ' (' 
                    + cast(staffADA as varchar(5)) + '%)') val,
                trend,
                srt  
        From    #temp
        Where   endYear >= 2018 
        Group by endYear, schoolLevelDescription, trend, srt 
        ) src
Pivot   (
        max(val)
        For endYear in ([2018], [2019], [2020], [2021]) 
        ) pvt 
Order by srt 

Drop Table #temp 

标签： tsql