Problems with PIVOT tables

I'm a having a problem with PIVOT tables.

I have a table that stores the results from multiple surveys, (see format below)...

 
SurveyID RespondantID QuestionID Answer========================================================= 1 1 1 X 1 1 2 Y 1 1 3 Z 1 2 1 R 1 2 2 S 1 2 3 T 14 10 1 A 14 10 2 B 14 11 1 C 14 11 2 D
 

I want to use a pivot table to convert the data it to a format like the following (for surveyID = 1)...

 
RespondantID Q1 Q2 Q3 =============================== 1 A B C 1 R S T
 

The 2 differences I need from a normal pivot query are...

(a) I don't want to summarise my results at all(b) I have multiple surveys in the same table so need to use an extra clause to pick out info for the survey I am interested in.

So far I have come up with...

SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]
As Q10 FROM (SELECT RespondantlD, QuestionlD, Answer FROM "3_Temp" WHERE SurveylD=3) AS preData PIVOT (
COUNT(Answer) FOR QuestionlD IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) ) AS data ORDER BV RespondantlD

But it doesn't work and I can't figure out why (error message when executing in VBA = "RunSQL function requires a SQL argument"

Can anyone help?

Thanks for posting sample data.

The PIVOT function is for SQL Server 2005, but you mention that you are excuting in VBA?

By the way, is this correct?

RespondantID Q1 Q2 Q3
===============================
1 A B C
1 2 R S T

If you need help for your question in either SQL Server 2005 or 2000, please post back.

|||

Yes that should be respondantID 2.

I am building an access data project (.adp) which links to tables in SQL Server 2005 so it is a SQL issue.

Thanks,

Martin

|||

Answer to your Question q: The aggregate function is required here, but you can use MAX instead of COUNT.

Answer to your Question b: No problem. As as you did in your query.

I don't know whether you can run this with your setup. Let us know if you find out.

I can run this in SQL Server 2005 Management Studio:

SELECT RespondantID, [1]As Q1, [2]As Q2, [3]As Q3, [4]As Q4, [5]As Q5, [6]As Q6, [7]As Q7, [8]As Q8, [9]As Q9, [10]

As Q10FROM(SELECT RespondantID, QuestionID, AnswerFROM [3_Temp]

WHERE SurveyID=1)AS preDataPIVOT

(MAX(Answer)FOR QuestionIDIN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]))

AS data

ORDERBY RespondantID

|||

Thanks for correcting my code Limno!

The only problem now is that I need to export the data to excel, but access will only do this from a table. I tried saving the above code as a view and then doing

SELECT *INTO tablenameFROM PivotedView

but I get an error saying "could not find PivotedView". Do you know how I can modify the pivot query directly, i.e.

SELECT RespondantID, [1]As Q1 ...INTO PivotedTable

Thanks!|||

This was resolved in post:http://forums.asp.net/t/1185101.aspx