sql - 在另一张表的许多行中排成1行

我有一个MSSQL数据库，该数据库在一个表中保存有关一个人的生物信息：

ID: Name : Age : Sex

在另一个表格中，该表格包含他们对以下多个问题的答案：

PersonID : QuestionID : Answer

是否可以通过MSSQLMSE将它们全部显示为一条记录，如下所示：

ID : Name : Age : Sex : Question1Answer : Question2Answer : Question3Answer : And so on?

最佳答案

试试这个：

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

这将为您提供：

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

但是：如果要对每个用户任意数量的问题动态地执行此操作，则可以执行以下操作：

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