How to perform a cross tab query without aggregating

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

SQL Server Development (2000)

How to perform a cross tab query without aggregating

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-31 : 11:01:50

Andre writes "

Production Platform:
-Windows Server 2003
-SQL Server 8.0

Development Platform:
-Windows XP 64Bit
-SQL Server Express 2005

Business Case:
A web/database application which stores user responses to surveys, where a survey is made up of a group of questions. Survey questions and user responses are stored as rows since they are dynamically created.

Problem:
The application must display user responses with one column for the user identification, and 1..n columns for each question per survey.

SQL Server Question:
Given the above problem, I've determined that I should develop a a stored procedure which performs a cross tab like query on two tables but without aggregating the values. I have not found anything on this but figure it must be a common situation.

Tables:
In a simplifed scenario I have two tables: Responses and Questions (see below).

[Responses]
-UserId (FK on another table)
-QuestionId (FK on Questions.Id)
-TextResponse //nullable
-BoolResponse //nullable
-NumericResponse //nullable

[Questions]
-Id
-QuestionLabel

Responses stores:
-Who responded to the question [UserId]
-The question it refers to [QuestionId]
-And the actual response: [TextResponse] or [BoolResponse] or [NumericResponse].

Questions stores:
-Id (PK)
-QuestionLabel (The actual question)

Please keep in mind that several columns have been removed since they are not relevant to this problem.

Desired Solution:
The desired output would be a table or data view which has the following schema:

[UserResponses]
-UserId
-QuestionResponse 1...N

A sample of what I would like to display would be like the following:


[UserId] |[QuestionLabel1] |[QuestionLabel2]  |[QuestionLabel3] 
--------------------------------------------------------
1        | Response 1      | Response 2       | false
--------------------------------------------------------
2        | Response 3      | Response 4       | true

So what I need to figure out is how to:
-Group by UserId
-Create columns for each QuestionLabel for the QuestionId in the given query
-Fill in each row with the matching response for the given Userid + QuestionId.
(I may simplify the Response table design to use one nchar column for storing responses instead of having 3.)

Solving this problem in code is not that hard to do, but it does not seem very efficient. I believe a stored procedure / database solution would be much more optimal. In addition, a more generic version which could be reused would have a lot of value to many people.

The closest solution I've found is this one:
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1131829,00.html?track=NL-464&ad=525294USCA"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-31 : 11:54:20

quote:

Solving this problem in code is not that hard to do, but it does not seem very efficient. I believe a stored procedure / database solution would be much more optimal. In addition, a more generic version which could be reused would have a lot of value to many people.

I strongly recommend avoiding dynamically cross tabbing data in T-SQL.

This is a presentation issue, solving the problem in code is exactly what you should be doing. It is easier, faster, cleaner and more flexible than doing it in T-SQL.

See: http://weblogs.sqlteam.com/jeffs/archive/2005/05/12/5127.aspx

for an example, some stats, and a few ideas. If you can handle this currently at your client, then you should keep doing it that way to keep your SQL code as clean and efficient as possible.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources