January 24, 2007 at 1:37 pm
I have a table that has results from a survey, Q1, Q2, ... Q10 where all the results for all 10 possible questions are in one record/row in the table.
I need to write a query that will put the results from Q1 thru Q4 only for Record 1 in Row 1, then results for Q2 for Record1 in Row2 and so on until there are no more results in the Q# field. There should only be rows where there is a value in the Q# field. If the Q# field is Null, no row should show up in the result set.
Getting the results for ALL Questions per row is easy enough with the following:
select d.field5, d.field2,
d.field6, d.crc, d.lc,
HD.KP1 AS Q1, HD.KP2 AS Q2, HD.KP3 AS Q3, HD.KP4 AS Q4
FROM Digits HD RIGHT JOIN Dial d ON d.DialID = HD.DialID
WHERE d.Projectid = 2407
My csv output should look like this:
Field5,Field2,Field6,CRC,LC,QuestionID,AnswerID,
ABC,5053000,S1/13/2007 5:31:27 PM14
ABC,5053000,S1/13/2007 5:31:29 PM25
ABC,5053000,S1/13/2007 5:31:38 PM30
ABC,5053000,S1/13/2007 5:31:43 PM41
Any help is most appreciated.
Thanks
Ron
January 24, 2007 at 2:20 pm
select d.field5, d.field2,
d.field6, d.crc, d.lc,
QuestionID, AnswerID
FROM (Select DialID , 1 as QuestionID, KP1 AS AnswerID
from Digits
where KP1 is not null
union all
Select DialID , 2, KP2 AS AnswerID
from Digits
where KP2 is not null
union all
Select DialID , 3, KP3 AS AnswerID
from Digits
where KP3 is not null
union all
Select DialID , 4, KP4 AS AnswerID
from Digits
where KP4 is not null
) as HD
RIGHT JOIN
Dial d ON d.DialID = HD.DialID
WHERE d.Projectid = 2407
Russel Loski, MCSE Business Intelligence, Data Platform
January 24, 2007 at 2:47 pm
Russel
Thanks, this still returned rows where the value was NULL for Q#? Joins are a weakness for me but should it be a left join?
Ron
January 24, 2007 at 8:43 pm
I assumed the join was correct in the original.
Try inner join.
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply