Hi,

this does the job

SELECT DISTINCT a.Surv_id, (SELECT ANSW FROM ANSWERS t WHERE t.QUES_ID = 1 AND t.SURV_ID = a.SURV_ID) [First Question], (SELECT ANSW FROM ANSWERS t WHERE t.QUES_ID = 2 AND t.SURV_ID = a.SURV_ID) [Second Question] FROM ANSWERS a

But the questions are "hard coded"... t.ID = 1 ..

With PIVOT the result is the same but, like all PIVOT, the columns (referring the questions) are also "hard coded"

SELECT SURV_ID, [First Question], [Second Question]

FROM

(SELECT s.SURV_ID, q.QUES, a.ANSW FROM

(

(SELECT DISTINCT SURV_ID FROM ANSWERS) s

CROSS JOIN Questions q

LEFT JOIN ANSWERS a ON a.SURV_ID = s.SURV_ID AND q.ID = a.QUES_ID

)

)

AS SrcData

PIVOT (

MAX(ANSW)

FOR QUES IN ([First Question], [Second Question])

) AS pivotData

You can build a dynamic SQL and execute it to build the query according to the existing questions.

Pedro