I have a SQL Server view with following data:
ID clientID surveyID questionID q_optionID q_ans_text
-----------------------------------------------------------------
1 1 1 1 NULL Yes
2 1 1 2 18 NULL
3 1 1 3 19 NULL
4 2 1 1 NULL No
5 2 1 2 18 NULL
6 2 1 3 19 NULL
7 3 2 1 NULL Yes
8 3 2 2 15 NULL
9 3 2 3 13 NULL
I want the result to be something like this:
ClientID SurveyID Q1 Q2 Q3
------------------------------------
1 1 Yes 18 19
2 1 No 18 19
3 2 Yes 15 13
Where the conditional NULL values are ignored and the proper answer is placed in the column. I have looked at Pivot table examples, but they seem to be focusing on single column pivots.
In order to perform this transformation you will need to UNPIVOT and then PIVOT the data. The UNPIVOT will take the values from your q_optionID and q_ans_text columns and transform it into two columns one with the value and the column name.
There are two ways that you can PIVOT this, you can hard-code all of the values using a static version or you can use dynamic sql. In order to UNPIVOT the data you need to be sure that the data is of the same datatype, so converting might be necessary.
Static PIVOT:
select clientid, surveyid,
questionid,
value,
col
from
(
select clientid, surveyid, questionid,
cast(q_optionID as varchar(4)) q_optionID,
q_ans_text
from yourtable
) s
unpivot
(
value
for col in (q_optionID, q_ans_text)
) un
Unpivot result:
| CLIENTID | SURVEYID | QUESTIONID | VALUE | COL |
---------------------------------------------------------
| 1 | 1 | 1 | Yes | q_ans_text |
| 1 | 1 | 2 | 18 | q_optionID |
| 1 | 1 | 3 | 19 | q_optionID |
| 2 | 1 | 1 | No | q_ans_text |
| 2 | 1 | 2 | 18 | q_optionID |
| 2 | 1 | 3 | 19 | q_optionID |
| 3 | 2 | 1 | Yes | q_ans_text |
| 3 | 2 | 2 | 15 | q_optionID |
| 3 | 2 | 3 | 13 | q_optionID |
Then you will apply the PIVOT to the result to get your final product.
select *
from
(
select clientid, surveyid,
'Q'+cast(questionid as varchar(10)) question,
value
from
(
select clientid, surveyid, questionid,
cast(q_optionID as varchar(4)) q_optionID,
q_ans_text
from yourtable
) s
unpivot
(
value
for col in (q_optionID, q_ans_text)
) un
) src
pivot
(
max(value)
for question in (Q1, Q2, Q3)
) piv
Dynamic PIVOT:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Q'+cast(questionid as varchar(10)))
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT clientid, surveyid,' + @cols + ' from
(
select clientid, surveyid,
''Q''+cast(questionid as varchar(10)) question,
value
from
(
select clientid, surveyid, questionid,
cast(q_optionID as varchar(4)) q_optionID,
q_ans_text
from yourtable
) s
unpivot
(
value
for col in (q_optionID, q_ans_text)
) un
) x
pivot
(
max(value)
for question in (' + @cols + ')
) p '
execute(@query)
UNION ALL/Aggregate with Case Version:
Now, if you are working in a system that does not have PIVOT then you can use a UNION ALL to UNPIVOT and an aggregate function with a CASE to PIVOT:
select clientid, surveyid,
max(case when questionid = 1 then value end) Q1,
max(case when questionid = 2 then value end) Q2,
max(case when questionid = 3 then value end) Q3
from
(
select clientid, surveyid, questionid, cast(q_optionID as varchar(10)) value, 'q_optionID' col
from yourtable
union all
select clientid, surveyid, questionid, q_ans_text value, 'q_ans_text' col
from yourtable
) unpiv
group by clientid, surveyid
All three will produce the same result:
| CLIENTID | SURVEYID | Q1 | Q2 | Q3 |
---------------------------------------
| 1 | 1 | Yes | 18 | 19 |
| 2 | 1 | No | 18 | 19 |
| 3 | 2 | Yes | 15 | 13 |