Posted by --CELKO-- on 03/30/07 13:22

>> 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]