SQL: How do I group these survey answers?

user_8988742 0
Viewed 159

I have a table that stores survey answers in the database:

╔════════════════╦════════════════╦════════════════╗
║   Question01   ║   Question02   ║   Question03   ║
╠════════════════╬════════════════╬════════════════╣
║ Certainly True ║ Certainly True ║ Certainly True ║
║ NULL           ║ Certainly True ║ NULL           ║
║ Partly True    ║ Partly True    ║ Certainly True ║
║ Certainly True ║ Certainly True ║ Certainly True ║
║ Certainly True ║ Certainly True ║ Certainly True ║
║ Certainly True ║ Partly True    ║ Certainly True ║
╚════════════════╩════════════════╩════════════════╝

How would I go about in grouping them in SQL to shows this:

+----------------+--+--+-------------+----------+--+-------------+--
| Certainly True  | Partly True | Not True | Do not Know | Missing |  
+-----------------+-------------+----------+-------------+---------+  
|                 |             |          |             |         |
|            214  |          12 |        0 |           0 |       1 |
|                 |             |          |             |         |
|            180  |          42 |        3 |           1 |       1 |
|                 |             |          |             |         |
|            223  |           2 |        0 |           1 |       1 |
|                 |             |          |             |         |
+-----------------+-------------+----------+--+-------------+-------

Where each row is the different questions and the columns are counts of the answers for that question.

I'm using SQL Manangement Studio.

Please show me the way. Thanks.



Update: How I solve this:

CREATE TABLE #temp1 (
Questions varchar(100),
Answers varchar(100) )

insert into #temp1 (Questions, Answers)
SELECT 'Question01' AS Questions, Question01 AS Answers FROM #Table1
UNION ALL               
SELECT 'Question02' AS Questions, Question02 AS Answers FROM #Table1
UNION ALL
SELECT 'Question03' AS Questions, Question03 AS Answers FROM #Table1

SELECT Questions,
   COUNT(CASE WHEN Answers = 'Certainly True' THEN 1 END) AS CertainlyTrue,
   COUNT(CASE WHEN Answers = 'Partly True' THEN 1 END) AS PartlyTrue,
   COUNT(CASE WHEN Answers = 'Not True' THEN 1 END) AS NotTrue,
   COUNT(CASE WHEN Answers = 'Do not Know' THEN 1 END) AS DoNotKnow,
   COUNT(CASE WHEN Answers IS NULL THEN 1 END) AS Missing,
   COUNT(questions) AS Total
FROM #temp1 
GROUP BY questions;

Might not be the most elegant way, but it works.

relational-database tsql sql-server sql
2 Answers

I would unpivot and use conditional aggregation:

select v.q,
       sum(case when v.v = 'Certainly True' then 1 else 0 end) as Certainly_True,
       sum(case when v.v = 'Partly True' then 1 else 0 end) as Partly_True,
       sum(case when v.v = 'Not True' then 1 else 0 end) as Not_True,
       sum(case when v.v = 'Do not Know' then 1 else 0 end) as Do_not_Know,
       sum(case when v.v is null then 1 else 0 end) as Missing
from t cross apply
     (values ('Question01', Question01),
             ('Question02', Question02),
             ('Question03', Question03),
     ) v(q, v)
group by v.q;
user_11440350

You could use:

WITH cte AS (
   SELECT 'Question1' AS grp, Question1 AS Question FROM tab UNION ALL
   SELECT 'Question2' AS grp, Question2 AS Question FROM tab UNION ALL
   SELECT 'Question3' AS grp, Question3 AS Question FROM tab 
)
SELECT grp,
   COUNT(CASE WHEN Question = 'Certainly True' THEN 1 END) AS Certainly_True,
   COUNT(CASE WHEN Question = 'Partly True' THEN 1 END) AS Partly_True,
   ...
   COUNT(CASE WHEN Question IS NULL THEN 1 END) AS missing
FROM table
GROUP BY grp;
user_50708790
Related