0

I have Survey results given to me in a massive table. (300+ columns)

A list of questions is provided in a table, and the Survey results in another.

Questions.

+-----+-----+---------------------------------+
| idx | QID |              QDesc              |
+-----+-----+---------------------------------+
|   1 | Q1  | Are you alive?                  |
|   2 | Q2  | Do you like pizza?              |
|   3 | Q3  | Do you like pineapple on pizza? |
+-----+-----+---------------------------------+

The answers from each person surveyed are entered on 1 line, each question/answer in it's own column.

+-----+----+-----+-----+
| idx | Q1 | Q2  | Q3  |
+-----+----+-----+-----+
|   1 |  1 | Yes | No  |
|   2 |  0 | No  | No  |
|   3 |  1 | Yes | Yes |
+-----+----+-----+-----+

I want to create a view on this which will be used in Excel Pivot tables. So I want the quest text in each row. Something like:

+-----+----------------+----------+--------------------+----------+---------------------------------+----------+
| idx |     Q1Desc     | Q1Result |       Q2Desc       | Q2Result |             Q3Desc              | Q3Result |
+-----+----------------+----------+--------------------+----------+---------------------------------+----------+
|   1 | Are you alive? |        1 | Do you like pizza? | Yes      | Do you like pineapple on pizza? | No       |
|   2 | Are you alive? |        0 | Do you like pizza? | No       | Do you like pineapple on pizza? | No       |
|   3 | Are you alive? |        1 | Do you like pizza? | Yes      | Do you like pineapple on pizza? | Yes      |
+-----+----------------+----------+--------------------+----------+---------------------------------+----------+

I feel like the answer should be a unpivot/pivot, but the thought is making my head explode. Over time i'll be loading more results and the questions and result columns could be added to, so i want it to be dynamic.

Is there a simple way to do this?

1
SELECT a.idx 
     , q1.qdesc q1desc, a.q1 q1result
     , q2.qdesc q2desc, a.q2 q2result 
     , q3.qdesc q3desc, a.q3 q3result
     , ...
FROM answers a
   , questions q1
   , questions q2 
   , questions q3
   , ...
WHERE q1.qid=1 
  AND q2.qid=2 
  AND q3.qid=3 
  AND ...

The number of questions/answers is dynamic, so use dynamic SQL for to build the query text.

PS. Do you really want to output the result table which contains 600 columns?

  • Thanks for your reply, Yes it works, but would still still to be updated/edited as columns are added. Re 600 Columns: Ideally not, but the idea is to create a view to feed multiple pivot tables. Users would be able to edit thier queries to cherry pick which fields they want. – Sir Swears-a-lot May 23 at 20:23
  • @SirSwears-a-lot would still still to be updated/edited as columns are added. Read one more time: use dynamic SQL for to build the query text. I.e. obtain fields list, build query text using common string manipulation operators into a variable, then EXEC this query. – Akina May 24 at 4:40
1

If I understood you correctly, I would suggest to use CASE statement plus MAX function

Below works for me:

;WITH Question AS 
(
    SELECT 1 AS idx, 'Q1' AS QID, 'Are you alive?' AS QDesc UNION ALL
    SELECT 2 AS idx, 'Q2' AS QID, 'Do you like pizza?' AS QDesc UNION ALL
    SELECT 3 AS idx, 'Q3' AS QID, 'Do you like pineapple on pizza?' AS QDesc
), Answer AS
(
    SELECT 1 AS idx, '1' AS Q1, 'Yes' AS Q2, 'No' AS Q3 UNION ALL
    SELECT 2 AS idx, '0' AS Q1, 'No'  AS Q2, 'No' AS Q3 UNION ALL
    SELECT 3 AS idx, '1' AS Q1, 'Yes' AS Q2, 'Yes' AS Q3
)
SELECT      a.Idx,
            MAX(CASE WHEN q.QID = 'Q1' THEN q.QDesc END) AS Q1Desc,
            MAX(a.Q1) AS Q1Answer,
            MAX(CASE WHEN q.QID = 'Q2' THEN q.QDesc END) AS Q2Desc,
            MAX(a.Q2) AS Q2Answer,
            MAX(CASE WHEN q.QID = 'Q3' THEN q.QDesc END) AS Q3Desc,
            MAX(a.Q3) AS Q3Answer
FROM        Answer a
CROSS JOIN  Question q
GROUP BY    a.Idx
  • I like the idea of the CTE, but the CASE is still hardcoded. meaning the view would need to be edited each time columns are added. – Sir Swears-a-lot May 23 at 20:25
  • so, in that case, you'll need to build dynamic queries though. – Dat Nguyen May 24 at 2:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.