Many Rows to a Single Row

[13Echo]

Ok, I am at a loose here, can someone help?

I have some data in a table which looks like this;

UserID Question Answer
68 1 2
68 2 3
68 3 Yes
69 1 3
69 2 2
69 3 No

The UserID, Question and Answer are varchars.

I need a View,Temp Table or write real time to a fixed width text file. The end result should resemble the output below.

UserID Question1 Question2 Question3
68 2 3 Yes
69 3 2 No

This is just a portion of the data, but if someone can tell me how to get the many rows for a User and their questions with answers to a Single row per user that would be outstanding.

Thank you for your help!
13Echo

 

[ThomVF]

If there are a fixed number of questions, you could use something like this (done for 3 questions):

SELECT UserID,
CASE Question WHEN '1' THEN Answer ELSE 0 END) AS 'Q1',
CASE Question WHEN '2' THEN Answer ELSE 0 END) AS 'Q2',
CASE Question WHEN '3' THEN Answer ELSE 0 END) AS 'Q3',
FROM QA_table
WHERE ((.....))
GROUP BY UserID

"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[ThomVF]

Bad syntax...corrected

SELECT UserID,
CASE Question WHEN '1' THEN Answer ELSE 0 END AS 'Q1',
CASE Question WHEN '2' THEN Answer ELSE 0 END AS 'Q2',
CASE Question WHEN '3' THEN Answer ELSE 0 END AS 'Q3',
FROM QA_table
WHERE ((.....))
GROUP BY UserID
"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[13Echo]

Thomas,

Thanks for the idea, but I have run into a couple problems. The following is my code
SELECT I_SRVY_USER_ID,

CASE N_SRVY_ACTUAL_NUM WHEN '1' THEN N_SRVY_ANSWER ELSE 0 END AS 'Q1',
CASE N_SRVY_ACTUAL_NUM WHEN '2' THEN N_SRVY_ANSWER ELSE 0 END AS 'Q2',
CASE N_SRVY_ACTUAL_NUM WHEN '3' THEN N_SRVY_ANSWER ELSE 0 END AS 'Q3'

FROM SRVY_RESULTS

WHERE I_SRVY_NAME_ID = 1

GROUP BY I_SRVY_USER_ID, N_SRVY_ACTUAL_NUM, N_SRVY_ANSWER

When I run it I get the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Everyday' to a column of data type int.

 

[JohnDTampaBay]

Here's one quick and dirty solution assuming one row per UserID + Question AND assuming every user will answer all three questions:

SELECT a1.UserID, 
 a1.Answer AS Question1,
 a2.Answer AS Question2,
 a3.Answer AS Question3
FROM (select * from MyTable where question=1) as a1
 JOIN (select * from MyTable where question=2) as a2
    ON a1.UserID = a2.UserID
 JOIN (select * from MyTable where question=3) as a3
    ON a1.UserID = a3.UserID

--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) selected

 

[ThomVF]

I apologize, I do not have a database handy to test my suggestions on.


Try changing the ELSE 0 to ELSE ''

Not sure if you want to group by all 3 columns as well. The examples I found only group by the driver column, UserID "Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[13Echo]

Angle and Tohomas,

Thank you for trying, I am thinking that I need to dump reults into a temp table. Once I have the results in the temp table, then I need to group them and insert them into another temp table or view. Thus, I would have all answers for a user in one row. Would using a cursor help, if so how would it work?

But, how to impliment it would be the tricky part