SQLize Online / PHPize Online  /  SQLtest Online

-- Hint: use Ctrl+Enter for SQL autocomplete with CTE_Student (id_mark, id_student, predmet, mark, date) as ( Select 1,1,'Математика',3,Cast(N'2021-03-23' as date) Union all Select 2,2,'Математика',4,Cast(N'2021-03-23' as date) Union all Select 3,3,'Математика',5,Cast(N'2021-03-23' as date) Union all Select 4,1,'Математика',3,Cast(N'2021-03-19' as date) Union all Select 5,2,'Математика',4,Cast(N'2021-03-18' as date) Union all Select 6,2,'Математика',4,Cast(N'2021-03-17' as date) ), CTE_Mark(id_student,fam,name,otch,klass) as ( Select 1,N'Петров',N'Иван',N'Иванович','5A' Union all Select 2,N'Сидоров',N'Петр',N'Иванович','5A' Union all Select 3,N'Смирнов',N'Федор',N'Иванович','5A' ) --Стандартный PIVOT SELECT fam, name, otch, IsNull([20210317],0) AS '2021-03-17', IsNull([20210318],0) AS '2021-03-18', IsNull([20210319],0) AS '2021-03-19', IsNull([20210323],0) AS '2021-03-23' FROM (SELECT fam, name, otch, mark, date FROM CTE_Student inner join CTE_Mark on CTE_Student.id_student = CTE_Mark.id_student) p PIVOT ( AVG(mark) FOR date IN ( [20210323], [20210319], [20210318], [20210317] ) ) AS pvt ORDER BY pvt.fam;