sql-server - 带有双标头的透视查询

Question

我准备了一个数据透视查询，它从一个简单的表

Team (NVarchar), ApptOutcome (NVarchar), PatientID (int), 'Under 16' As LeftColumn.

输入数据..

North Team, Attended, 1000
North Team, DNA, 1000
South Team, DNA, 2000
South Team, Cancelled, 3000

etc etc..

我想要一个输出计算PatientID每个团队（只有 2 个）、每个结果（只有 3 个结果Attended、、、、DNA）Cancelled和左侧的 LeftColumn！

Team所以基本上我需要一个使用and的双标题Outcome。到目前为止，我已经为团队工作了！请参阅下面的代码和输出...

SELECT 
    Team, [Attended] AS Attended, [DNA] AS DNA, [Cancelled] AS Cancelled
FROM 
    (SELECT 
         Team, ApptOutcome, PatientID
     FROM 
         Under16) ps
PIVOT
    (COUNT(PatientID)
         FOR ApptOutcome IN ( [Attended], [DNA], [Cancelled])
    ) AS pvt

电流输出：

    Team    Attended    DNA Cancelled
  North Team    299     21     22
  South Team    10      5       1

预期输出：

                  Attended                  DNA               Cancelled

Left Column    NorthTeam, SouthTeam, NorthTeam, SouthTeam, NorthTeam, SouthTeam

Under 16         100          150     20        21        2         4

标签： sql-serverpivot