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.

Answer 1

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 |