SQL Query Help

[joeymains]

In the below query, I'm trying to use a case statement to pull answers to questions submitted in a form that flows into separate rows of the table.

For example, the table is laid out as this:

complaint_ID,question,answer
1234,DOB,1/1/2020
1234,SSN,5678
1234,Mobile_Number,4255551212

What I want to get is just one row:
1234,1/1/2020,5678,4255551212

The result I get from the below query is:

complaint_ID,DOB,SSN,Mobile_No
1234,1/1/2020,NULL,NULL
1234,NULL,5678,NULL
1234,NULL,NULL,4255551212


Query:

select complaint_ID,
case when Question_ID = 'Complaint_Mobile_Number' then Answer end as 'Mobile_No',
case when Question_ID = 'DOB' then Answer end as 'DOB',
case when Question_ID = 'SSN' then Answer end as 'SSN'
FROM [dbo].[vw_puerto_rico_lifeline_form]
where answer is not null

 

[Peter Schmitz]

You would have to pivot your results to get the answer to show up as a column.