Make 1 row out of many rows from another table

join, pivot, sql, sql-server, sql-server-2008

Solution

Try this:

SELECT ID, name, age, sex, Question1answer, Question2answer
FROM
(
  SELECT 
    p.Id,
    p.Name,
    p.Age,
    p.sex,
    questionanswer = 'Question' + CAST(q.questionid AS VARCHAR(10)) + 'answer',
    q.Answer
  FROM Persons p 
  INNER JOIN Questions q ON p.Id = q.UserID
) t
PIVOT
(
  MAX(Answer)
  FOR questionanswer IN([Question1answer], [Question2answer])
 ) p;

SQL Fiddle Demo

This will give you:

| ID |     NAME | AGE | SEX | QUESTION1ANSWER | QUESTION2ANSWER |
-----------------------------------------------------------------
|  1 |    Ahmed |  25 |   M |             Yes |              No |
|  2 | Mohammed |  30 |   M |              No |           Never |
|  3 |     Sara |  25 |   F |              No |           Never |

However: If you want to do this dynamically for any number of questions per user, you can do this:

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

select @cols = STUFF((SELECT distinct 
                        ',' +
                        QUOTENAME('Question' + 
                                  CAST(questionid AS VARCHAR(10)) + 
                                  'answer')
                FROM questions
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


SET @query = 'SELECT ID, name, age, sex, ' + @cols +
             ' FROM
              (
                SELECT 
                  p.Id,
                  p.Name,
                  p.Age,
                  p.sex,
                  questionanswer = ''Question'' +
                                   CAST(q.questionid AS VARCHAR(10)) + 
                                   ''answer'',
                  q.Answer
                FROM Persons p 
                INNER JOIN Questions q ON p.Id = q.UserID
              ) t
              PIVOT
              (
                MAX(Answer)
                FOR questionanswer IN( ' + @cols + ') ) p ';

SQL Fiddle Dynamic Demo

Problem

I have a MSSQL database which in one table holds bio info about a person: ``` ID: Name : Age : Sex ``` In another table it holds their answers to a number of questions like this: ``` PersonID : QuestionID : Answer ``` Is it possible to display all of them via MSSQLMSE into one record like this: ``` ID : Name : Age : Sex : Question1Answer : Question2Answer : Question3Answer : And so on? ```

Original source