locked
Special Pivot Table RRS feed

  • Question

  • User1623084388 posted

    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

    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.

    Code Response Value
    ADCFD A Yes
    ADCFD B No
    ADCFD C NULL

    Thank you for your help

    Thursday, August 20, 2020 9:09 PM

Answers

  • User77042963 posted
     
    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
      

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 20, 2020 10:22 PM

All replies

  • User77042963 posted
     
    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
      

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 20, 2020 10:22 PM
  • User1623084388 posted

    Thank you limno.

    Friday, August 21, 2020 1:49 AM