Using UNPIVOT with Dynamic SQL

Question

I have a table with this schema:

tblResults

Question1 | Question2 | Question3 | etc | etc | Question240 |

In these columns values can be of the following:

1, 2, 3, 4, N, M

I need to present the data like the this:

| QuestionNumber | 1 | 2 | 3 | 4 | N | M |
------------------------------------------
| Question1      | 53| 27| 10| 5 | 2 | 3 |
| etc            | 20| 40| 32| 8 | 0 | 0 | <-- These values being % (but I can do the calculation later).
| etc            |

I need to be able to control how many rows the result set will output. I have done this by doing the following (3 columns only):

DECLARE @cname VARCHAR(MAX)
SELECT @cname = STUFF((
    SELECT ', ' + COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_NAME = 'tblResults') AND (ORDINAL_POSITION BETWEEN 8 AND 10)
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1,1,'')

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT Answers, CASE WHEN Answer = '''' THEN ''N'' ELSE Answer END AS Answer, COUNT(Answer) AS Total
            FROM (
                SELECT '+@cname+'
                FROM tblResults
                WHERE (something = ''006'') AND (somethingElse = ''ABC'')
                ) AS MyTable
            UNPIVOT (Answer FOR Answers IN ('+@cname+')) AS MyUnPivot
            GROUP BY Answers, Answer
            ORDER BY Answers, Answer'

exec sp_executesql @sql

This produces the following result set:

| Answers | Answer | Total |
----------------------------
|Question1|    1   | 12474 |
|Question1|    2   | 188   |
|Question1|    3   | 200   |
|Question1|    4   | 5     |
|Question1|    N   | 0     |
|Question1|    M   | 142   |
|Question2|    1   | 14521 |
|etc      |        |       |
|etc      |        |       |

Hence my plan was to use dynamic SQL as I can't think of any other way to do this. I have tried various UNPIVOT methods but I cannot seem to make any progress.

Beofre anybody suggests what a terrible design this is, I know, it's something I've inherited and it cannot be changed not without rewritting a 3rd party application.

If anybody can think of a better title please edit.

Thanks.

Can you post some additional sample data? What is becoming the new columns headers and what are the values under each header? — Taryn, Jun 05 '13 at 11:15
@bluefeet I've added what I have so far, the `COUNT`'s are correct but I need to get the values of `Answer` as columns. — Jack Pettinger, Jun 05 '13 at 11:19
Based on the result set that your current query produces, what do you want the final result to be? — Taryn, Jun 05 '13 at 11:27
@bluefeet Look at the 3rd code block in my question, sorry for the confusion. Columns: `QuestionNumber, 1, 2, 3, 4, N, M` — Jack Pettinger, Jun 05 '13 at 11:30
What you are showing is not clear, you want the `answer` values as the column headers. What is a question has more than one answer? What do you want as the column headers? Please expand on the details because it is not very clear. Do you want all of the distinct answers as the new columns? What if you have 1000's of distinct answers. — Taryn, Jun 05 '13 at 11:38
@bluefeet Yes, I need the values as columns. The questions are multiple choice, so the values with only ever be: `1, 2, 3, 4, N or M`. `N` being None and `M` being multiple answers provided. There will only ever be 6 answers. — Jack Pettinger, Jun 05 '13 at 11:46

score 1 · Accepted Answer · answered Jun 05 '13 at 11:54

Based on the information that you provided you need to PIVOT the answers values into columns. Since your answers are a static value (1, 2, 3, 4, N, M) then you can hard code these values into your query.

You will still need to use dynamic SQL to unpivot the correct columns though. The code will be similar to the following:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsUnpivot = STUFF((SELECT distinct ','+ quotename(c.column_name)
                   from INFORMATION_SCHEMA.COLUMNS as C
                   where (TABLE_NAME = 'tblResults') and
                         (c.ORDINAL_POSITION BETWEEN 3 AND 5)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select questionNumber, [1], [2], [3], [4], [N], [M]
     from
     (
        select questionNumber, answer
        from
        (
          select '+@colsUnpivot+'
          from tblResults
        ) x
        unpivot
        (
          answer
          for questionNumber in ('+ @colsunpivot +')
        ) u
      ) d
      pivot
      (
        count(answer)
        for answer in ([1], [2], [3], [4], [N], [M])
      ) piv'

exec(@query);

See SQL Fiddle with Demo. This gives a result:

| QUESTIONNUMBER | 1 | 2 | 3 | 4 | N | M |
------------------------------------------
|      Question3 | 1 | 1 | 1 | 1 | 1 | 1 |
|      Question4 | 1 | 0 | 1 | 2 | 1 | 1 |
|      Question5 | 1 | 1 | 1 | 0 | 1 | 2 |

Thank you very much, perfect. Can you explain PIVOT and UNPIVOT, I have used UNPIVOT before, but I don't understand how this works in this scenario, using the two together. — Jack Pettinger, Jun 05 '13 at 13:25
@JackPettinger The UNPIVOT is basically normalizing your data from multiple columns into multiple rows. Once you get the result into the multiple rows you want to display the total count for each answer as a column. The PIVOT uses an aggregate function `count()` and then you provide the list of values that you want to convert into columns, this is your answer list. Hope that helps, let me know if you have any other questions. — Taryn, Jun 05 '13 at 13:49

Using UNPIVOT with Dynamic SQL

1 Answers1