CODEWITHSUNDEEP
sqlsql-server
Neil Norris
Neil Norris

Reputation: 411

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

Upvotes: 0

Views: 65

Answers (2)

SqlZim
SqlZim

Reputation: 38053

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;

Brad Schulz on cross apply: http://bradsruminations.blogspot.com/search/label/CROSS%20APPLY

Upvotes: 2

nimdil
nimdil

Reputation: 1381

Sean is correct. It will go like this:

with subquery as (
  select year, month, survey, question, tempVal from #table
  unpivot
  (tempVal for question in (q1, q2, q3, q4, q5, q6, q7, ..., q29)) as up
)
select year, month, survey, question,
  sum(case when tempVal = 1 then 1 else 0 end) as a1,
  sum(case when tempVal = 2 then 1 else 0 end) as a2,
  sum(case when tempVal = 3 then 1 else 0 end) as a3,
  sum(case when tempVal = 4 then 1 else 0 end) as a4,
  sum(case when tempVal = 5 then 1 else 0 end) as a5
from subquery
group by year, month, survey, question

Upvotes: 1

Related Questions