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

Problems with an XML column....?

I have a table with an XML column that stores report definitions. For development purposes I have to delete and reinsert these reports on a fairly regular basis (300+ reports). When I do so, I notice the following: The size of the data file increases by roughly 30MB. The file I am loading the XML from (thru an SSIS package) is only 4MB. If I shrink the database, I can get rid of 20MB, but I still net a 10MB increase. The package gets run frequently. Right now my database is at about 250MB when it should be about 40-50MB. I am trying to avoid having to drop all constraints, truncating the table, and reapplying the constraints. If I disable the step in my package that deals with the XML column, I see no change in the database. It appears to be the DELETE that causes the most damage, as I have tested the package on purely an insert-basis and on a delete-insert-basis. Has anyone seen behavior like this?Since no one has responded in over a month, I am going to close this one out myself. I ended up dropping all of the constraints, truncating, and reapplying the constraints. I still have some growth in my data files but it has slowed considerably. I read that empty pages could be left behind by a DELETE statement, but I have never since file growth like this. Perhaps, SP1 addresses this. I don't know.