Requête croisée avec des colonnes dynamics dans SQL Server 2005

Il existe deux façons d'effectuer une statique PIVOT où vous PIVOT dur les valeurs et dynamic où les colonnes sont déterminées lorsque vous exécutez.

Même si vous voulez une version dynamic, il est parfois plus facile de commencer avec un PIVOT statique et de travailler ensuite vers un PIVOT dynamic.

Version statique:

 SELECT studentid, name, sex,[C], [C++], [English], [Database], [Math], total, average from ( select s1.studentid, name, sex, subjectname, score, total, average from Score s1 inner join ( select studentid, sum(score) total, avg(score) average from score group by studentid ) s2 on s1.studentid = s2.studentid ) x pivot ( min(score) for subjectname in ([C], [C++], [English], [Database], [Math]) ) p 

Voir SQL Fiddle avec démo

Maintenant, si vous ne connaissez pas les valeurs qui seront transformées, vous pouvez utiliser Dynamic SQL pour cela:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(SubjectName) from Score FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT studentid, name, sex,' + @cols + ', total, average from ( select s1.studentid, name, sex, subjectname, score, total, average from Score s1 inner join ( select studentid, sum(score) total, avg(score) average from score group by studentid ) s2 on s1.studentid = s2.studentid ) x pivot ( min(score) for subjectname in (' + @cols + ') ) p ' execute(@query) 

Voir SQL Fiddle avec démo

Les deux versions produiront les mêmes résultats.

Juste pour compléter la réponse, si vous n'avez pas de fonction PIVOT , vous pouvez get ce résultat en utilisant CASE et une fonction d'agrégat:

 select s1.studentid, name, sex, min(case when subjectname = 'C' then score end) C, min(case when subjectname = 'C++' then score end) [C++], min(case when subjectname = 'English' then score end) English, min(case when subjectname = 'Database' then score end) [Database], min(case when subjectname = 'Math' then score end) Math, total, average from Score s1 inner join ( select studentid, sum(score) total, avg(score) average from score group by studentid ) s2 on s1.studentid = s2.studentid group by s1.studentid, name, sex, total, average 

Voir SQL Fiddle avec démo

Cela nécessite la construction d'une string de requête SQL à l'exécution. Les noms de colonne, les counts et les types de données dans SQL Server sont toujours statiques (la raison la plus importante en est que l'optimiseur doit connaître le stream de données de la requête au moment de l'optimization).

Je vous recommand donc de créer une requête PIVOT à l'exécution et de l'exécuter via sp_executesql . Notez que vous devez coder en dur les valeurs de la colonne pivot. Faites attention à leur échapper correctement. Vous ne pouvez pas utiliser de parameters pour eux.

Vous pouvez également créer une telle requête par nombre de colonnes et utiliser des parameters uniquement pour les valeurs de pivot. Vous devrez atsortingbuer des noms de colonne factices comme Pivot0, Pivot1, ... Vous avez toujours besoin d'un model de requête par nombre de colonnes. Sauf si vous êtes prêt à coder en dur le nombre maximum de colonnes pivot dans la requête (disons 20). Dans ce cas, vous pouvez utiliser du SQL statique.

Vous devez utiliser SQL PIVOT dans ce cas. Plese référer le lien suivant:

Pivot sur le nombre de colonnes inconnu

Pivoter deux ou plusieurs colonnes dans SQL Server

Pivots avec des colonnes dynamics dans SQL Server