you could create a table with a record for each question...1-50, then do a cross join on the original table and use case statements to get the answer...the case statement will get kind of ugly, but it should work...
so you have tblanwers...uniqueID q1 q2 q3 ... q50 and tblquestions...questionnumber select tblanswers.uniqueid, tblquestions.questionnumber, CASE tblquestions.questionnumber WHEN 1 THEN q1 WHEN 2 THEN q2 WHEN 3 THEN q3 ....etc etc END AS ANSWER FROM tblanswers CROSS JOIN tblquestions On 3/7/06, Robert Everland III <[EMAIL PROTECTED]> wrote: > I have come across this a few times and was wondering how some of you would > attack it. > > I have a table that looks like this > > > uniqueID q1 q2 q3 ... q50 > > In reality the table should look like this > > uniqueID studentID answer qid > > > I need it to have multiple records so I can do a join on a table that holds > the answer which is written as a table with multiple records as opposed to a > table with multiple columns.I know I can accomplish this in coldfusion. The > way I did it was to query the big table, then create a structure so each > column was in a structure. What would you have done? > > > > Bob > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234422 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
