J'ai une table de questions, qui a un nombre inconnu de questions (premier tableau dans la figure)
J'ai aussi une feuille de réponse, qui enregistre la réponse de l'élève à la question (deuxième tableau de la figure)
Create table Question ( Id int, Text nvarchar(50), PRIMARY KEY (Id) ) Create table AnswerSheet ( StudentId int, QuestionId int, Answer nvarchar(50), PRIMARY KEY (StudentId,QuestionId), FOREIGN KEY (QuestionId) REFERENCES Question (Id) ) insert into Question values(1,'What''s your age'), (2,'What''s your gender'), (3,'When do you go home'), .... insert into AnswerSheet values(500,1,'20'), (500,2,'Male'), (500,3,'5:00pm'), (501,1,'50'), (502,2,'I don''t know@@'), ....
Comment écrire un SQL pour générer une table comme celle-ci?
StudentId What's your age What's your gender When do you go home ... --------- ---------------- ------------------- ------------------- 500 20 Male 5:00pm ... 501 50 NULL NULL 502 NULL I don''t know@@ NULL ...
Je pense que Pivot est prometteur mais je ne suis pas sûr de savoir comment l'utiliser, en particulier PIVOT
nécessite une fonction d'agrégation mais mes données ne sont pas des nombres.
En supposant que vous vouliez aller dynamic
Exemple
Declare @SQL varchar(max) = Stuff((Select ',' + QuoteName(Text) From Question Order by ID For XML Path('')),1,1,'') Select @SQL = ' Select * From ( Select StudentID ,Col = B.Text ,Value = A.Answer From AnswerSheet A Join Question B on A.QuestionID=B.ID ) A Pivot (max(Value) For [Col] in (' + @SQL + ') ) p' Exec(@SQL);
Résultats
StudentID What's your age What's your gender When do you go home 500 20 Male 5:00pm 501 50 NULL NULL 502 NULL I don't know@@ NULL
Si cela aide, le SQL généré ressemble à ceci
Select * From ( Select StudentID ,Col = B.Text ,Value = A.Answer From AnswerSheet A Join Question B on A.QuestionID=B.ID ) A Pivot (max(Value) For [Col] in ([What's your age],[What's your gender],[When do you go home]) ) p
Je sais que cette question est répondue par un accepté, mais j'espère que cette approche aide les autres.
simplement vous pouvez atteindre votre objective sans utiliser Pivot
, via l'utilisation de Group by
comme suivant: –
Select b.StudentId, Min(Case a.text When 'What''s your age' Then b.answer End) 'What''s your age', Min(Case a.text When 'What''s your gender' Then b.answer End) 'What''s your gender', Min(Case a.text When 'When do you go home' Then b.answer End) 'When do you go home' from Question a inner join AnswerSheet b on a.id = b.Questionid Group By StudentId
et vous avez mentionné un nombre inconnu de questions , de sorte que le code suivant pour dynamic: –
DECLARE @DynamicQuestions VARCHAR(8000) SELECT @DynamicQuestions = Stuff( (SELECT N' Min(Case a.text When''' + replace (Text,'''','''''') + ''' Then b.answer End) ''' + replace (Text,'''','''''') + ''',' FROM Question FOR XML PATH(''),TYPE) .value('text()[1]','nvarchar(max)'),1,1,N'') select @DynamicQuestions = left(@DynamicQuestions,len(@DynamicQuestions)-1) -- for Removing last comma exec ('Select b.StudentId, '+ @DynamicQuestions + 'from Question a inner join AnswerSheet b on a.id = b.Questionid Group By StudentId' )
Résultat : –
StudentId What's your age What's your gender When do you go home 500 20 Male 5:00pm 501 50 NULL NULL 502 NULL I don't know@@ NULL