Having problems creating an SQL statement

I am having trouble getting the SQL statement to return stats from a survey the way I want them. The table is set up as:

ID Q1 Q2 Q3 Q4

Responses for each question (Columns Q1 – Q4) will be a numerical value between 1-5. I want to count how many 1s, 2s, 3s, etc. I have tried different joins, self joins, unions and sub selections but cannot get the correct output.

I would like to get the output for each question as a single record, and if possible have a final column with an average for the question. But I can do that in the data binding if needed.

Qs Ones Twos Threes Fours Fives

Q1 #of 1s #of 2s #of 3s #of 4s #of 5s

Q2 #of 1s #of 2s #of 3s #of 4s #of 5s

Q3 #of 1s #of 2s #of 3s #of 4s #of 5s

Any tips or SQL sample statements would be greatly appreciated.

It looks like 2 pivots will be needed to to transpose the data from your table layout to your desired output layout. If you're using Sql 2005, there is a Pivot feature but here i'll show you how you could do this using syntax that will work for Sql2000 or Sql2005.

I'll break each step down into it's own View. Each view will build off of the previous view(s).

The 1st View is named: VIEW_SurveyRotation1
In this View, we turn all the column headings (Q1, Q2 etc..) into data values and move all the answers into a single Answer column. This is actually the opposite of what we normally consider a pivot, but i still tend to think of it as a [reverse] pivot.

SELECT'Q1'AS Qs, Q1AS answerFROM dbo.SurveyUNIONALLSELECT'Q2'AS Qs, Q2AS answerFROM dbo.SurveyUNIONALLSELECT'Q3'AS Qs, Q3AS answerFROM dbo.SurveyUNIONALLSELECT'Q4'AS Qs, Q4AS answerFROM dbo.Survey

The 2nd View is named: VIEW_SurveyRotation2
In this View, we pivot the answer values back into column headings by querying against the results of our first View. As mentioned, there's more than one way to create a Pivot in Sql.

SELECT Qs,CASEWHEN answer = 1THEN 1ELSE 0END AS One,CASEWHEN answer = 2THEN 1ELSE 0END AS Two,CASEWHEN answer = 3THEN 1ELSE 0END AS Three,CASEWHEN answer = 4THEN 1ELSE 0END AS Four,CASEWHEN answer = 5THEN 1ELSE 0END AS FiveFROM dbo.VIEW_SurveyRotation1

The 3rd View is named: VIEW_SurveyAverages
Here we can create a simple set of Averages by querying against our first View

SELECT Qs,AVG(CAST(answerAS decimal))AS [Avg]FROM dbo.VIEW_SurveyRotation1GROUP BY Qs

The 4th and final View is named: VIEW_SurveyResult
In this View, we summarize the answer counts for each column and also join in the Averages

SELECT dbo.VIEW_SurveyRotation2.Qs, dbo.VIEW_SurveyAverages.[Avg],SUM(dbo.VIEW_SurveyRotation2.One)AS Ones,SUM(dbo.VIEW_SurveyRotation2.Two)AS Twos,SUM(dbo.VIEW_SurveyRotation2.Three)AS Threes,SUM(dbo.VIEW_SurveyRotation2.Four)AS FoursFROM dbo.VIEW_SurveyRotation2INNERJOIN dbo.VIEW_SurveyAveragesON dbo.VIEW_SurveyRotation2.Qs = dbo.VIEW_SurveyAverages.QsGROUP BY dbo.VIEW_SurveyRotation2.Qs, dbo.VIEW_SurveyAverages.[Avg]

I tend to work with complex sql queries by breaking it down into steps like this. It helps me to achieve the desired result. Then, once you've got it working, you can review it and see if you can eliminate any of the steps by consolidating them into fewer queries.

|||

Here is the code sample for UNPIVOT and PIVOT solution with SQL Server 2005:

SELECT Questionas Qs, [1]as Ones, [2]as Twos, [3]as Threes, [4]as Fours, [5]as FivesFROM

(SELECT Question, [Value]FROM pivotQuestions

UNPIVOT([Value]FOR [Question]in([Q1], [Q2], [Q3], [Q4], [Q5], [Q6])

)as unpvt) t

PIVOT(COUNT([Value])FOR [Value]IN([1], [2], [3], [4], [5])

)as pvt

--Table and test data

CREATETABLE [dbo].[pivotQuestions](

[ID] [int]NotNULL,

[Q1] [int]NULL,

[Q2] [int]NULL,

[Q3] [int]NULL,

[Q4] [int]NULL,

[Q5] [int]NULL,

[Q6] [int]NULL

)

GO

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(1, 3, 4, 5, 5, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(2, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(3, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(4, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(5, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(6, 3, 4, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(7, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(8, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(9, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(10, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(11, 5, 1, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(12, 2, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(13, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(14, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(15, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(16, 5, 1, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(17, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(18, 5, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(19, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(20, 5, 5, 5, 5, 5, 5)

|||

Thanks for the tips, I will give them a try.

|||

Do not have SQL 2005 so I did what you suggested mbanavige. I was able to combine it all into a single query and not use Views, just couldnt add in the averages that. But that was easy enough to do on the databind. Working like a charm. thanks.

|||

limno:

Here is the code sample for UNPIVOT and PIVOT solution with SQL Server 2005:

SELECT Questionas Qs, [1]as Ones, [2]as Twos, [3]as Threes, [4]as Fours, [5]as FivesFROM

(SELECT Question, [Value]FROM pivotQuestions

UNPIVOT([Value]FOR [Question]in([Q1], [Q2], [Q3], [Q4], [Q5], [Q6])

)as unpvt) t

PIVOT(COUNT([Value])FOR [Value]IN([1], [2], [3], [4], [5])

)as pvt

Hi,

I'm having a really similar problem, also with surveys.

The only 2 differences are that (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...

TABLE

=====

SurveyID RespondantID QuestionID Answer

PIVOT QUERY

===========

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.

What am I doing wrong?