Posted by Matt F on 03/30/07 15:40

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...@earthlink.net> wrote:
> >> I can't quite get my mind wrapped around this one. <<
> >From your example, the three answers seem to be drawn from different
>
> domains:
>
> 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.

[Back to original message]