从另一个表中的许多行中生成一行

Question

我有一个MSSQL数据库，它在一个表中保存了一个人的个人简历信息：

ID: Name : Age : Sex

在另一个表中，它保存了他们对一些问题的答案，例如：

PersonID : QuestionID : Answer

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

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

Answer 1

试试这个：

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

Answer 2

select ID,Name,Age,Sex,
(select top 1 Answer from Questions 
               where PersonId=prerson.id AND QuestionID=1) as Question1Answer,
(select top 1 Answer from Questions 
               where PersonId=prerson.id AND QuestionID=2) as Question2Answer,
(select top 1 Answer from Questions 
               where PersonId=prerson.id AND QuestionID=3) as Question3Answer

from prerson

Answer 3

sql server中的PIVOT命令适用于转置表结果，请参见http://msdn.microsoft.com/en-us/library/ms177410(v=sql.100).aspx。

下面的脚本处理一组预定义的问题。对于数量可变的列，此链接将有助于http://blog-mstechnology.blogspot.com/2010/06/t-sql-pivot-operator-with-dynamic.html。

 DECLARE @Person TABLE(
    ID INT,
    Name VARCHAR(50),
    Age INT,
    Sex CHAR(1)
)

DECLARE @Questions TABLE(
    ID INT,
    QuestionID INT,
    Question VARCHAR(200),
    Answer VARCHAR(200)

)

INSERT INTO @Person VALUES
(1,'Andrew',56,'M'),
(2,'Marge',65,'F')

INSERT INTO @Questions VALUES
(1,1,'Question1','Andrews Answer 1'),
(1,2,'Question2','Andrews Answer 2'),
(2,1,'Question1','Marge Answer 1'),
(2,3,'Question3','Marge Answer 3')


SELECT ID,Age,Name,Sex,
[1] AS 'Question1Answer',
[2] AS 'Question2Answer',
[3] AS 'Question3Answer'
FROM(
SELECT P.ID,P.Age,P.Name,P.Sex,Q.QuestionID,Q.Answer
    FROM @Person P
    INNER JOIN @Questions Q  ON Q.ID = P.ID
) Source
PIVOT
(
    MAX(Answer)
    FOR QuestionID IN ([1],[2],[3])
)AS PT