There is a table of performance. Fields:

ZachetkaId,SemesterId,DisciplinaId,Ocenka

I need to flip the table, so that the lines are zachetka (then I drag it through Fio's join), and the columns of the discipline. The fact is that such a code does this, but you need to know in advance which disciplines to work with, and I need a universal query, where I wouldn’t explicitly specify the discipline

 SELECT * FROM ( SELECT zachetka,disciplina,ocenka FROM uspevaemostocenki WHERE disciplina IN('29','32','35','42') ) AS pr PIVOT ( max(ocenka) FOR disciplina IN([29],[32],[35],[42]) ) AS pvt

then replace with

 SELECT * FROM ( SELECT zachetka,disciplina,ocenka FROM uspevaemostocenki WHERE disciplina IN(**select distinct disciplina from disciplini**) ) AS pr PIVOT ( max(ocenka) FOR disciplina IN([29],[32],[35],[42]) ) AS pvt

And I also need the same in the pivot request, but I can't insert the same request ...

  • @ Roman Rakzin The code is formatted with the 101010 button in the editor. - Nicolas Chabanovsky ♦

1 answer 1

Found the answer, if anyone is interested ...

 DECLARE @cols VARCHAR(1000) DECLARE @sqlquery VARCHAR(2000) SELECT @cols = STUFF(( SELECT distinct ',' + QuoteName([nazvaniedisciplini]) FROM disciplini FOR XML PATH('') ), 1, 1, '') SET @sqlquery = 'SELECT * FROM (SELECT s.Fio, ocenka,nazvaniedisciplini FROM uspevaemostocenki as Uo inner join students as S on uo.zachetka=s.zachetka inner join Disciplini as D on d.Disciplina=uo.disciplina) base PIVOT (SUM(ocenka) FOR [nazvaniedisciplini] IN (' + @cols + ')) AS finalpivot' EXECUTE ( @sqlquery )