Pivot Table SQL

Question

I have 3 tables.

1 - Student

| student_code |  name   |  
--------------------------
| 1            |  katia  |   
| 2            |  roger  |   
| 3            |  ken    | 

2 - Question

| question_code|  question       |  
----------------------------------
| 1            |  father' name   |   
| 2            |  favorite fruit |   
| 3            |  first teacher  | 

3 - Answers

| student_code | question_code | answer  |  
-------------------------------------------
| 1            | 1             |  katia  |    
| 1            | 2             |  banana |   
| 2            | 1             |   ken   | 

When I join this 3, I've 800.000 rows like this:

| name  | question        | answer  |  
-------------------------------------
| katia| favorite fruit   |  banana |   
| katia| father's name    |  paul   |   
| roger| father's name    |  aaron  | 

I need convert the questions rows for columns and maintain only 1 student per row.

I've searched for pivot table but I can't make it works.

OBS: Not every student has all questions

EDIT: I think this question is not duplicated because PIVOT function is very specific , the other question doesn't answer the mine.

Answer 1

Since you are using SQL Server 2008, you can use the PIVOT function to get the result. This function will convert your rows of data into columns through the use of an aggregate function. Since your data is a string, then you will be limited to using either the max or min aggregate functions.

If you have a limited number of columns, then you can hard-code your query. The basic syntax will be:

select name,
  [father' name], 
  [favorite fruit],
  [first teacher]
from
(
  select q.question,
    a.answer,
    s.name
  from student s
  left join answers a
    on s.student_code = a.student_code
  left join question q
    on a.question_code = q.question_code 
) d
pivot
(
  max(answer)
  for question in ([father' name], [favorite fruit],
                   [first teacher])
) piv;

See SQL Fiddle with Demo.

But if you have an unknown number of questions, then you will need to use dynamic SQL to generate the result. This will create a list of the questions that you want to have in the final result and you will create a sql string that will be executed. The code will be similar to:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(question) 
                    from question
                    group by question_code, question
                    order by question_code
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT name, ' + @cols + N' 
            from 
            (
              select q.question,
                a.answer,
                s.name
              from student s
              left join answers a
                on s.student_code = a.student_code
              left join question q
                on a.question_code = q.question_code 
            ) x
            pivot 
            (
                max(answer)
                for question in (' + @cols + N')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. Both will give a result:

|  NAME | FATHER' NAME | FAVORITE FRUIT | FIRST TEACHER |
|-------|--------------|----------------|---------------|
| katia |        katia |         banana |        (null) |
|   ken |       (null) |         (null) |        (null) |
| roger |          ken |         (null) |        (null) |