I found many resources regarding Pivot tables yet still have not found one that fits my situation. I have constructed a query that would provide the following data.
ID Name address answer Question QuestionID
1 client1 caddress1 answer1 Question Text 1
28 client2 c2address1 answer1 Question Text 1
57 client3 c3address1 answer1 Question Text 1
73 client4 c4address1 answer1 Question Text 1
77 client5 c5address1 answer1 Question Text 1
1 client1 caddress1 answer2 Question Text2 5
28 client2 c2address1 answer2 Question Text2 5
57 client3 c3address1 answer2 Question Text2 5
73 client4 c4address1 answer2 Question Text2 5
77 client5 c5address1 answer2 Question Text2 5
I am trying to display it like this:
ID Name address Question Text Question Text2 Question Text3
1 client1 caddress1 answer1 answer2 answer3
28 client2 c2address1 answer1 answer2 answer3
The columns with the 'Question Text' will need to be dynamic, in other words their could be 2 of them or there could be 10, based on the users query.
Currently using MS SQL 2008 and the data will be displayed within a standard ASP page and not sure if I could do this with a Stored Procedure or some other means.
Keep in mind the users will have the ability to select the questions (via QuestionID) that they need to see the results of.
Any help is appreciated.