This is how the data is organized:

vID Answer

12 Satisfied
12 Marketing
12 Yes
15 Dissatisfied
15 Technology
15 No
32 Strongly Dissatisfied
32 Marketing
32 Yes

What I need to do is pull a recordset which each vID is a single row
and each of the answers is a different field in the row so it looks
something like this

vID Answer1 Answer2 Answer3

12 Saitsfied Marketing Yes

etc...


I can't quite get my mind wrapped around this one.

Examples:
http://builder.com.com/5100-6388_14-6143761.html

PIVOT will only work if you have SQL SERVER 2005 otherwise you will
use the CASE method.

On Mar 29, 12:43 pm, "franzey" <fran... (AT) gmail (DOT) com> wrote:

Thanks for the link. I do only have SQL Server 2000 so the PIVOT is
out. Looks like I will be doing some CASE statements. I'm new at this.
Do you know the general syntax I would use using the above example to
get this rolling?




On Mar 29, 1:15 pm, "Jason Lepack" <jlep... (AT) gmail (DOT) com> wrote:

I've never done it myself. I just found the examples and tried them
and they worked. If you're dealing with too big of a dataset then it
may be tedious.

I've found some more that are more dynamic. Here's the google search
string I used:

pivot table sql server

Cheers,
Jason Lepack

On Mar 29, 2:07 pm, "franzey" <fran... (AT) gmail (DOT) com> wrote:

franzey (franzey (AT) gmail (DOT) com) writes:
From what you posted, there is nothing to identify which is Answer1, 2
and 3. Had there been, you could have run this query:

SELECT vID,
Answer1 = MAX(CASE answerno WHEN 1 THEN Answer END),
Answer2 = MAX(CASE answerno WHEN 2 THEN Answer END),
Answer3 = MAX(CASE answerno WHEN 3 THEN Answer END)
FROM tbl
GROUP BY vID

The MAX() here is a trick: we know that there is only one entry per
vID, so we could have used MIN() instead. MAX() is only there to get
all items on the same line.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

customer_status, department, response

Therefore, you should have seperate columns for them from the start
and get rid of this non-table completely. This thing never made it to
1NF. YOu also semto assume that a table has an ordering, so that
talking about first, second and third anssers makes sense -- it doe
snot.

We can make some wild guesses about using CASE expressions to put your
vague, generic answers into categories, but that is not the real
problem.

SELECT v_id, MAX (CASE WHEN answer IN ('yes', 'no')
THEN answer ELSE NULL END) AS response,
etc.
FROM Foobar
GROUP BY v_id;

The CASE expressions would be converted into CHECK () constriants in
proper DDL.

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote


I mean I know it's messy and all ;-).



--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

I did a poor job explaining the situation. I think my simple example
has only muddied the water, so to speak.


You are correct in that the information is coming from different
tables. The people "upstairs" have been using a web-based survey
system that dumps the results into SQL. The problem, is that I am
trying to run reports on some of the information and the way the data
is laid out is making it difficult. For example, the survey in
question is a short 4 question survey with 2 radio selection groups
and then there are 2 text field entries. I am only concerned with the
2 radio selection questions. Question 1 has 4 radio options and
question 2 has 5. The problem here is that when the system submits the
data to the DB, it creates a record for EACH answer on the survey. For
example, the VoterAnswers table will have the VoterID, AnswerText,
etc... for each question on the survey with the VoterID remaining the
same. This is what is looks like:

VoterID AnswerText AnswerID
5 Comments here 2058
5 <NULL> 2057
5 <NULL> 2059


AnswerID is found in the Answer table which corresponds (in this case)
to 2057="Technology" and 2059="Satisfied"


What I need is one recordset with the following format.

VoterID Department Rating
---------- ----------------- ------------
5 Technology Satisfied


Does this clear anything up?


On Mar 30, 9:22 am, "--CELKO--" <jcelko... (AT) earthlink (DOT) net> wrote:

Matt F wrote:

Insert standard "post the CREATE TABLE statement for the Answer table"
rant here. Assuming that it looks something like this:

AnswerID | AnswerType | AnswerValue
---------+------------+------------
2057 | Department | Technology
2059 | Rating | Satisfied

then you can do:

select va.VoterID,
ad.AnswerValue as Department,
ar.AnswerValue as Rating
from VoterAnswers va
join Answer ad on va.AnswerID = ad.AnswerID
and ad.AnswerType = 'Department'
join Answer ar on va.AnswerID = ar.AnswerID
and ar.AnswerType = 'Rating'

Actually the Answer Table doesn't have the AnswerType





On Mar 30, 2:29 pm, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote: