Help for this query!!! - 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 2000 Forums

Transact-SQL (2000)

Help for this query!!!

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2002-10-09 : 23:37:53

I had this table called maISv_brgEntrySurveyQuestionAnswer

EntryID; SurveyID; QuestionID; AnswerOptionID
1; 1; 1; 2
1; 1; 2; 3
2; 1; 1; 3
2; 1; 2; 4

I need to somehow to make it view/stored procedure to display like this in tsql rather doing in scripting language:

EntryID; SurveyID; Question_1; Question_2
1; 1; 2; 3
2; 1; 3; 4

Any ideas? Or perhaps I need somehow to use Temp Table and Cursor maybe? Any guidance or anything ... I'll apreciated that.

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-10 : 07:24:23

This should help you get started:

http://www.sqlteam.com/item.asp?ItemID=2955

Also look up "CASE" and "cross tab" in Books Online, there are examples there of how this procedure works, you might be able to simplify the code a bit.

dewacorp.alliances

452 Posts

Posted - 2002-10-10 : 21:58:03

Thanks for the "CASE" and "cross tab" .

But now I had complex table than before which has an extra of AnswerText (varchar 200)

EntryID; SurveyID; QuestionID; AnswerOptionID; AnswerText; SequenceQuestionNo
1; 1; 1; 2; 1
1; 1; 2; 3; 2
1; 1; 3; 'Description 1'; 3
2; 1; 1; 3; 1
2; 1; 2; 4; 2
1; 1; 3; 'Description 2'; 3

The entry that I want is like this:

EntryID; SurveyID; Question_1; Question_2; Question_3
1; 1; 2; 3; 'Description 1'
2; 1; 3; 4; 'Description 2'

Since using CrossTab technique with group by EntryID

SELECT EntryID,
SUM(CASE SequenceNo WHEN 1 THEN AnswerOptionID ELSE Null END) AS Question_1,
SUM(CASE SequenceNo WHEN 4 THEN AnswerOptionID ELSE Null END) AS Question_2,

CASE SequenceNo WHEN 3 THEN AnswerText ELSE Null END AS Question_3

FROM maISv_brgSurveyEntryQuestionAnswer
GROUP BY EntryID

Since the SequenceNo and AnswerText is not numeric it will have an error for aggregat fucntion:

Column 'maISv_brgSurveyEntryQuestionAnswer.AnswerOptionID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'maISv_brgSurveyEntryQuestionAnswer.SequenceNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there any way to make to get this around?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-10 : 22:21:46

Use MAX or MIN instead of SUM. For each column that is NOT being pivoted you need to include them in the GROUP BY clause as well.

Edited by - robvolk on 10/10/2002 22:22:46

dewacorp.alliances

452 Posts

Posted - 2002-10-10 : 22:41:13

Hi robvolk

You give me the idea .. I try this query:

SELECT EntryID,
SUM(CASE SequenceNo WHEN 1 THEN AnswerOptionID ELSE Null END) AS Question_1,
SUM(CASE SequenceNo WHEN 2 THEN AnswerOptionID ELSE Null END) AS Question_2,
MIN(CASE SequenceNo WHEN 3 THEN AnswerText ELSE Null END) AS Question_3,
FROM maISv_brgSurveyEntryQuestionAnswer
GROUP BY EntryID

for AnswerText (varchar) works ok ... the result is like this:

EntryID; Question_1; Question_2; Question_3
1 ; 1 ; 2 ; 'Description 1'

Thanks for your help.

Valdy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources