Combine multiple records into one

[John]

Hi,

I have a data table that looks like this:

ID First Last Q1 Q2 Q3 Q4
-----------------------------------------------------
2 John Doe Y
2 John Doe Y
2 John Doe N
2 John Doe Y

The table is the output from an IVR phone tree survey. For some reason
the telephony software we are using creates a new line in the table
each time a person chooses to repeat a question or option group. This
is a worst case example, but it could happen. For reporting purposes I
need to combine these lines into one record for each ID so I can
compile the statistics accordingly.

I need to design a query if possible that will accomplish this?

Can anyone help me?

Thanks

 

[pietlinden]

Union the Non-null question answers together. Join on ID

 

[Duane Hookom]

I don't care much for the structure but you could try:
SELECT ID, [First], [Last], Max(Q1) as Max1, Max(Q2) as Max2,Max(Q3) as
Max3,Max(Q4) as Max4
FROM tblThatLooksLikeThis
GROUP BY ID, [First], [Last];