sql - 枢轴以避免 SQL 中的大量连接？

Question

我有下表:

student teacher grade   gradedate
--------------------------------------
1       ALICE   A       05.08.2016
1       BOB     A       25.01.2015
1       CHARLES C       12.05.2017
1       DAVID   B       25.09.2013
2       BOB     D       01.02.2014
2       CHARLES A       26.04.2016
2       DAVID   C       02.05.2016

(student,teacher)是这张表的主键。

我想生成这样的结果

student ALICEGrade  ALICEGradeDate  BOBGrade    BOBGradeDate    CHARLESGrade    CHARLESGradeDate    DAVIDGrade  DAVIDGradeDate
-----------------------------------------------------------------------------------------------------------------------------------------------------------
1       A           05.08.2016      A           25.01.2015      C               12.05.2017          B           25.09.2013
2       NULL        NULL            D           01.02.2014      A               26.04.2016          C           02.05.2016

我设法通过为每位教师使用 join 子句来生成它:

SELECT st.student, 
a.grade as [ALICEGrade], a.gradedate as [ALICEGradeDate], 
b.grade as [BOBGrade], b.gradedate as [BOBGradeDate],
c.grade as [CHARLESGrade], c.gradedate as [CHARLESGradeDate],
d.grade as [DAVIDGrade], d.gradedate as [DAVIDGradeDate] 
FROM
(SELECT distinct [student] FROM [dbo].[TESTGRADETABLE]) st
LEFT join [dbo].[TESTGRADETABLE] a on a.teacher = 'ALICE' and a.student = st.student 
LEFT join [dbo].[TESTGRADETABLE] b on b.teacher = 'BOB' and b.student = st.student 
LEFT join [dbo].[TESTGRADETABLE] c on c.teacher = 'CHARLES' and c.student = st.student
LEFT join [dbo].[TESTGRADETABLE] d on d.teacher = 'DAVID' and d.student = st.student

但我想知道是否有另一种更优雅的解决方案来避免大量连接(真正的请求有大约 10 个连接)。我正在考虑从以下位置开始使用数据透视表:

SELECT * FROM [dbo].[TESTGRADETABLE] 
pivot
(
    max(grade)
    for  teacher in ([ALICE],[BOB],[CHARLES],[DAVE])
) piv1

但是我被困在这里了。我不知道是否可以用它生成 TeacherGradeDate 列。

创建表和数据的TSQL:

CREATE TABLE [dbo].[TESTGRADETABLE](
    [student] [int] NOT NULL,
    [teacher] [varchar](50) NOT NULL,
    [grade] [char](1) NOT NULL,
    [gradedate] [date] NOT NULL,
 CONSTRAINT [PK_TESTGRADETABLE] PRIMARY KEY CLUSTERED 
(
    [student] ASC,
    [teacher] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO dbo.[TESTRATINGTABLE]
           ([student]
           ,[teacher]
           ,[grade]
           ,[gradedate])
     VALUES
           (1,'ALICE','A','2016-08-05'),
           (1,'BOB','A','2015-01-25'),
           (1,'CHARLES','C','2017-05-12'),
           (1,'DAVID','B','2013-09-25'),           
           (2,'BOB','D','2014-02-01'),
           (2,'CHARLES','A','2016-04-26'),
           (2,'DAVID','C','2016-05-02')

Answer 1

无需创建两个枢轴。可以通过 Dynamic Pivot 实现预期的结果。

示例

Declare @SQL varchar(max) = '
Select *
 From (
        Select B.*
         From  YourTable A
         Cross Apply (values (student,teacher+''Grade'',cast(grade as varchar(max)))
                            ,(student,teacher+''GradeDate'' ,cast(gradedate as varchar(max)))
                     ) B (student,item,value)
      ) A
 Pivot (max([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName(concat(teacher,'Grade')) 
                                                              +','+QuoteName(concat(teacher ,'GradeDate')) 
                                               From YourTable  
                                               Order By 1 
                                               For XML Path('')),1,1,'')  + ') ) p'
Exec(@SQL);
--Print @SQL

返回

生成的 SQL 如下所示:

Select *
 From (
        Select B.*
         From  YourTable A
         Cross Apply (values (student,teacher+'Grade',cast(grade as varchar(max)))
                            ,(student,teacher+'GradeDate' ,cast(gradedate as varchar(max)))
                     ) B (student,item,value)
      ) A
 Pivot (max([Value]) For [Item] in ([ALICEGrade],[ALICEGradeDate],[BOBGrade],[BOBGradeDate],[CHARLESGrade],[CHARLESGradeDate],[DAVIDGrade],[DAVIDGradeDate]) ) p

“提供”PIVOT 生成以下内容的子查询