SQL query to return a report of two tables

pivot, sql, sql-server-2008, t-sql

Solution

You can use the `PIVOT` function to transform this data. It seems rather unusual that you have the `Name` (Firstname/Lastname) as the headers but if that is what you want, then you can use the following:

select *
from
(
  select a.questionnumber,
    u.firstname +' '+ u.lastname as Name,
    a.answer
  from answers a
  left join users u
    on a.userid = u.userid
) src
pivot
(
  max(answer)
  for name in ([John Smith], [Bob Jones])
) piv;

See SQL Fiddle with Demo

Result:

| QUESTIONNUMBER | JOHN SMITH | BOB JONES |
-------------------------------------------
|              1 |       blah |      test |
|              2 |      hsdfk |    (null) |

I would think you would want the `Question Numbers` as the headers with the answers below them like this:

select *
from
(
  select 'QuestionNumber'+cast(a.questionnumber as varchar(10)) questionnumber,
    u.firstname +' '+ u.lastname as Name,
    a.answer
  from answers a
  left join users u
    on a.userid = u.userid
) src
pivot
(
  max(answer)
  for questionnumber in ([QuestionNumber1], [QuestionNumber2])
) piv;

See SQL Fiddle with Demo

Result:

|       NAME | QUESTIONNUMBER1 | QUESTIONNUMBER2 |
--------------------------------------------------
|  Bob Jones |            test |          (null) |
| John Smith |            blah |           hsdfk |

The above answers will work great if you have a known number of items to transform. But if you have an unknown number, then you can use dynamic SQL to `PIVOT` the data:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('QuestionNumber'+cast(questionnumber as varchar(10))) 
                    from answers
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Name, ' + @cols + ' from 
             (
              select ''QuestionNumber''+cast(a.questionnumber as varchar(10)) questionnumber,
                u.firstname +'' ''+ u.lastname as Name,
                a.answer
              from answers a
              left join users u
                on a.userid = u.userid
            ) x
            pivot 
            (
                max(answer)
                for questionnumber in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

Result:

|       NAME | QUESTIONNUMBER1 | QUESTIONNUMBER2 |
--------------------------------------------------
|  Bob Jones |            test |          (null) |
| John Smith |            blah |           hsdfk |

Problem

Original source