Dynamic Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Columns

Author

Topic

trabart
Starting Member

1 Post

Posted - 2009-04-22 : 05:52:38

We have a database where we store the multiple choice answers of students of the exams they have taken. Each exam can have 1 to n questions.
We now have a query that creates the following output when we want to see the results of a certain Exam (ExamId =1):
StudentId,ExamId,NumberOfQuestions,QuestionId,GivenAnswer
1,1,3,1,A
1,1,3,2,B
1,1,3,3,C
2,1,3,1,A
2,1,3,2,A
2,1,3,3,A

I would now like to have a query that produces the following output for this particular exam:
StudentId,ExamId,Answer1,Answer2,Answer3
1,1,A,B,C
2,1,A,A,A
So basically what I'm trying to achieve is that the 'NumberOfQuestions' column in my first query will be used to generate the right number of Answer-columns in my 2nd query and that each student will then get his or her 'own' record.

Is this at all possible without using dynamic sql? And if not: how can we build a stored procedure that limits the chance of sql injection?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 06:01:25

See this topic (without the view reference) how to build things the way you want
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123850

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-22 : 06:38:30

[code]
DECLARE @T TABLE(StudentId INT,ExamId INT,NumberOfQuestions INT,QuestionId INT,GivenAnswer CHAR(2))
INSERT INTO @t SELECT 1,1,3,1,'A'
INSERT INTO @t SELECT 1,1,3,2,'B'
INSERT INTO @t SELECT 1,1,3,3,'C'
INSERT INTO @t SELECT 2,1,3,1,'A'
INSERT INTO @t SELECT 2,1,3,2,'A'
INSERT INTO @t SELECT 2,1,3,3,'A'

SELECT studentid, examid , [1]AS 'Answer1',[2] AS 'Answer2',[3] AS 'Answer3'
FROM @t
PIVOT(MAX(givenanswer) FOR questionid IN ([1],[2],[3]))p

SELECT studentid,examid,
MAX(CASE WHEN questionid = 1 THEN givenanswer END) AS 'Answer1',
MAX(CASE WHEN questionid = 2 THEN givenanswer END) AS 'Answer2',
MAX(CASE WHEN questionid = 3 THEN givenanswer END) AS 'Answer3'
FROM @t
GROUP BY studentid,examid
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources