Neil Norris Neil Norris - 17 days ago 3
SQL Question

Rearrange Dataset

I am working on some survey data and was wondering if i could rearrange the data to make it a lot more usable. The results are classified as 1-5 and I would like the preferred table to count the results by value and group by question.

original table:

year | month | customer_id | survey | q1 | q2 | q3 | q4 | q5 | q6 ----> q29
-----|-------|-------------|--------|----|----|----|----|----|---
2016 | Oct | ABC12345678 | 1 | 1 | 2 | 3 | 1 | 2 | 3
2016 | Oct | DEF12345678 | 1 | 2 | 1 | 4 | 2 | 1 | 1
2016 | Oct | GHI12345678 | 1 | 4 | 2 | 1 | 1 | 3 | 2
2016 | Oct | JKL12345678 | 1 | 2 | 3 | 2 | 4 | 1 | 3
2016 | Oct | MNO12345678 | 1 | 5 | 2 | 3 | 1 | 2 | 3
2016 | Oct | PQR12345678 | 1 | 3 | 4 | 4 | 2 | 4 | 4
2016 | Oct | STU12345678 | 1 | 1 | 5 | 3 | 1 | 2 | 5
2016 | Oct | VWX12345678 | 1 | 2 | 2 | 4 | 2 | 1 | 1


Preferred Table:

Year | Month | Survey | Question | 1 | 2 | 3 | 4 | 5 |
-----|-------|--------|----------|----|----|----|----|----|
2016 | Oct | 1 | q1 | 80 | 45 | 25 | 63 | 89 |
2016 | Oct | 1 | q2 | 65 | 75 | 35 | 53 | 69 |


I can do this with a basic select query but to do it for every question will end up with 29 unions and there must be a quicker way.

Regards,

Neil

Answer

This is what I would use until someone posts a better solution:

    <!-- language: lang-sql -->

use tempdb;
create table #tempsurvey (year int, month varchar(32), customer_id varchar(32), survey int, [q1] int, [q2] int, [q3] int, [q4] int, [q5] int, [q6] int, [q7] int, [q8] int, [q9] int, [q10] int, [q11] int, [q12] int, [q13] int, [q14] int, [q15] int, [q16] int, [q17] int, [q18] int, [q19] int, [q20] int, [q21] int, [q22] int, [q23] int, [q24] int, [q25] int, [q26] int, [q27] int, [q28] int, [q29] int);
insert into #tempsurvey values (2016,'Oct', 'ABC12345678', 1, 1,2,3,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3,1,2);
insert into #tempsurvey values (2016,'Oct', 'DEF12345678', 1, 4,5,1,4,5,1,4,5,1,4,5,1,4,5,1,4,5,1,4,5,1,4,5,1,4,5,1,4,5);

with cte as (
  select t.[year], t.[month], t.customer_id, t.survey, x.question, x.answer
    from #tempsurvey t
      cross apply (values ('q1',q1) ,('q2',q2) ,('q3',q3) ,('q4',q4) ,('q5',q5) ,('q6',q6) ,('q7',q7) ,('q8',q8) ,('q9',q9) ,('q10',q10) ,('q11',q11) ,('q12',q12) ,('q13',q13) ,('q14',q14) ,('q15',q15) ,('q16',q16) ,('q17',q17) ,('q18',q18) ,('q19',q19) ,('q20',q20) ,('q21',q21) ,('q22',q22) ,('q23',q23) ,('q24',q24) ,('q25',q25) ,('q26',q26) ,('q27',q27) ,('q28',q28) ,('q29',q29)) 
      as x (Question,Answer)
 )
  select [year], [month], [survey], question, [1]=sum(case when answer=1 then 1 else 0 end), [2]=sum(case when answer=2 then 1 else 0 end), [3]=sum(case when answer=3 then 1 else 0 end), [4]=sum(case when answer=4 then 1 else 0 end), [5]=sum(case when answer=5 then 1 else 0 end)
    from cte
      group by [year], [month], [survey], question;

  drop table #tempsurvey;