Combining data from 3 tables to 1 output table

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)

Combining data from 3 tables to 1 output table

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-09-27 : 16:48:42

Hey there. Me again with another issue that I'm probably just over-thinking:

I need to create a table of users and their answers to a questionnaire. I need the output to look like this:


userID | userNAME |  1  |  2   |  3  |  4  
-------------------------------------------
    1  |  John    | Yes | NULL | Yes | No
    2  |  Mike    | Yes | No   | Yes | Yes

Questions that are not answered are not in the database and should return a NULL (As shown above). and questions can be added at any time. The output should have a column for each of the questions (so in the example there's 4 questions. If a fifth question is added then there needs to be a fifth column.) The question columns can be either the questionID or the questionTEXT, that does not matter.

The data is stored in 3 tables. a User table, a question table and an answers table.


CREATE TABLE #users (
	userID INT,
	userNAME VARCHAR(15)
)

CREATE TABLE #questions (
	questionID INT,
	questionTEXT varchar(255)
)	

CREATE TABLE #answers (
	answerID INT,
	userID INT,
	questionID INT,
	answerTEXT VARCHAR(255)
)

Sample data is as follows:


INSERT INTO #users (userID, userNAME) VALUES (1,'John')
INSERT INTO #users (userID, userNAME) VALUES (2,'Mike')
INSERT INTO #questions (questionID, questionTEXT) VALUES (1,'Do you...')
INSERT INTO #questions (questionID, questionTEXT) VALUES (2,'Can you...')
INSERT INTO #questions (questionID, questionTEXT) VALUES (3,'Will you...')
INSERT INTO #questions (questionID, questionTEXT) VALUES (4,'Did you...')
INSERT INTO #answers (answerID, userID, questionID, answerTEXT) VALUES (1,1,1,'Yes')
INSERT INTO #answers (answerID, userID, questionID, answerTEXT) VALUES (2,1,3,'Yes')
INSERT INTO #answers (answerID, userID, questionID, answerTEXT) VALUES (3,1,4,'No')
INSERT INTO #answers (answerID, userID, questionID, answerTEXT) VALUES (4,2,1,'Yes')
INSERT INTO #answers (answerID, userID, questionID, answerTEXT) VALUES (5,2,2,'No')
INSERT INTO #answers (answerID, userID, questionID, answerTEXT) VALUES (6,2,3,'Yes')
INSERT INTO #answers (answerID, userID, questionID, answerTEXT) VALUES (7,2,4,'Yes')

What's the best way to accomplish this in MS SQL 2000? I have tried using multiple cursors (One to build a temp table with dynamic column names and another to build and execute INSERT statements, but it isn't looping through all the rows. I'm curious if there's an easier way to do this.

Thanks in advance!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-27 : 17:15:30

Since you posted in 2000 group here is a solution without using 2K+ PIVOT:


select u.userid
       ,u.UserName
       ,max(case when q.questionid = 1 then answerText end) [1]
       ,max(case when q.questionid = 2 then answerText end) [2]
       ,max(case when q.questionid = 3 then answerText end) [3]
       ,max(case when q.questionid = 4 then answerText end) [4]
from   #users u
cross join #questions q
left outer join #answers a
       on a.userid = u.userid
       and a.questionid = q.questionid
group by u.userid
       ,u.username

OUTPUT:
userid      UserName        1           2          3       4
----------- --------------- -----------------------------------
1           John            Yes         NULL       Yes     No
2           Mike            Yes         No         Yes     Yes

EDIT:
BTW, I changed Mike's userid to "2" in your sample data insert.

Be One with the Optimizer
TG

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-09-27 : 18:56:58

Unfortunately your query only works if there are 4 questions. Users can add questions at any time, so I need it to be able to scale on-the-fly to any number of questions currently in the database.

When a fifth question is added your query will only show the four questions. I do see that I could count the questions in the page code (currently ASP) and then dynamically add the

,max(case when q.questionid = n then answerText end) [n]

line, but I'd rather have a complete SQL solution.

(and thanks.. I meant to enter 2 for mike. updated it in my test code,but forgot to update it after pasting into the post)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 20:44:32

quote:
When a fifth question is added your query will only show the four questions. I do see that I could count the questions in the page code (currently ASP) and then dynamically add the

,max(case when q.questionid = n then answerText end) [n]

line, but I'd rather have a complete SQL solution.

Isn't that a complete solution ? Either you do that in your ASP code or in T-SQL query, you are still using Dynamic SQL.

Take a look here on the dynamic SQL query
The Curse and Blessings of Dynamic SQL

KH
[spoiler]Time is always against us[/spoiler]

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-09-27 : 23:08:16

quote:
Originally posted by khtan

Isn't that a complete solution ? Either you do that in your ASP code or in T-SQL query, you are still using Dynamic SQL.

It is and it isn't. The existing routines on the site take a single SQL query and generate XML, JSON, HTML tables or an Excel XLS file from the input. If it can be done strictly in MS SQL i don't have to modify the code, but if it's that difficult or memory intensive, I just might re-write the page to do just that.

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-09-28 : 10:27:02

Okay I take it back. Revisiting the source code this morning, it's actually pretty easy to implement this way. Thanks so much for helping me look at the solution in a new way.

I am still curious if there's a way to do it strictly in SQL, but for the project I'm working on, this works great. I appreciate the help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-28 : 10:51:43

There is a way to pivot dynamically in sql. Check out this section in:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Output / Reporting / Formatting

Article: Dynamic Cross-Tabs/Pivot Tables (NC)
More Dynamic CrossTabs using T-SQL 04Aug2006 (Jeffs Blog)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources