I have the following SQL server table. ResponseNo 1 and 2 are related to QuestionA, ResponseNo 3 and 4 are related to QuestionB, and ResponseNo 5 and 6 are related to QuestionC.

IF ResponseNo = 1 and Val = 1 Then Question = A and value = Yes
IF ResponseNo = 2 and Val = 1 Then Question = A and value = No
IF ResponseNo = 3 and Val = 1 Then Question = B and value = Yes
IF ResponseNo = 4 and Val = 1 Then Question = B and value = No
IF ResponseNo = 5 and Val = 1 Then Question = C and value = Yes
IF ResponseNo = 6 and Val = 1 Then Question = C and value = No

Code

ResponseNo

Val

ADCFD

1

1

ADCFD

2

0

ADCFD

3

0

ADCFD

4

1

ADCFD

5

0

ADCFD

6

0

Now, I would like to pivot the table so that I get one response for each question. Here is the final answer.

create table test
(
Code varchar(10),ResponseNo int, Val bit)
Insert into test values('ADCFD',1,1)
,('ADCFD',2,0)
,('ADCFD',3,0)
,('ADCFD',4,1)
,('ADCFD',5,0)
,('ADCFD',6,0);
select Code,
Case when ResponseNo IN( 1,2) Then 'A'
when ResponseNo IN( 3,4) Then 'B'
when ResponseNo IN( 5,6) Then 'C'
else null end Question,
Max(Case when ResponseNo IN(1,3,5) and Val=1 Then 'Yes'
when ResponseNo IN(2,4,6) and Val=1 Then 'No'
else null end) Val
from test
Group by
Code,
Case when ResponseNo IN( 1,2) Then 'A'
when ResponseNo IN( 3,4) Then 'B'
when ResponseNo IN( 5,6) Then 'C'
else null end
drop table test

Member

10 Points

45 Posts

## Special Pivot Table

Aug 20, 2020 09:09 PM|psarr|LINK

Hello everyone,

I have the following SQL server table. ResponseNo 1 and 2 are related to QuestionA, ResponseNo 3 and 4 are related to QuestionB, and ResponseNo 5 and 6 are related to QuestionC.

IF ResponseNo = 1 and Val = 1 Then Question = A and value = Yes

IF ResponseNo = 2 and Val = 1 Then Question = A and value = No

IF ResponseNo = 3 and Val = 1 Then Question = B and value = Yes

IF ResponseNo = 4 and Val = 1 Then Question = B and value = No

IF ResponseNo = 5 and Val = 1 Then Question = C and value = Yes

IF ResponseNo = 6 and Val = 1 Then Question = C and value = No

Now, I would like to pivot the table so that I get one response for each question. Here is the final answer.

Thank you for your help

All-Star

123252 Points

10024 Posts

Moderator

## Re: Special Pivot Table

Aug 20, 2020 10:22 PM|limno|LINK

Format your SQL query with instant sql formatter:

http://www.dpriver.com/pp/sqlformat.htm

Member

10 Points

45 Posts

## Re: Special Pivot Table

Aug 21, 2020 01:49 AM|psarr|LINK

Thank you limno.