pivot to avoid numerous joins in TSQL?

Question

I have the following table :

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) is the primary key of this table.

And I want to generate a result like this

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

I managed to produce it by using join clause for each teacher:

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

But I was wondering if there is another more elegant solution to avoid the numerous joins ( the real request has around 10 joins). I was thinking to use pivot starting from:

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

but I am stuck here. I do not know if it possible to generate TeacherGradeDate columns with it.

The TSQL to create table and data:

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')

pivot to avoid numerous joins in TSQL?

4 Answers