sql-server - 如何检索多个详细记录和主记录作为单行？

Question

我有两个结构与此类似的表:

人:

ID   Name   Age
 1   Jack    25
 2   Jill    23

测试:

ID  PersonID  TestID  Result
 1         1       1     125
 2         1       2     120
 3         1       3      75
 4         2       1      90
 5         2       2      95
 6         2       3     7.2

有没有办法用一条语句检索该数据，使主表中的每条记录都显示在一行中？像这样的东西:

PersonID  Name  Age  Test1  Test2  Test3
       1  Jack   25    125    120     75
       2  Jill   23     90     95    7.2

到目前为止，我想出的唯一方法是创建一个函数，该函数遍历详细记录并填充临时表。不是很优雅。

提前致谢

Answer 1

为了得到这个结果，你需要使用 PIVOT 功能。这会将数据从多行转换为列。

如果您提前知道这些值，或者您将获得有限数量的 TestId值，然后您可以对查询进行硬编码，使查询成为静态。

SELECT Name,
  Age,
  [1] AS Test1,
  [2] AS Test2,
  [3] AS Test3 
FROM
( 
  SELECT P.Name, P.Age, t.TestID, t.Result 
  FROM tests t
  INNER JOIN person P 
  ON p.ID = t.PersonID
) T 
PIVOT
(
  sum(Result) 
  FOR TestID IN ([1], [2], [3])
) piv;

见 SQL Fiddle with Demo .

但是如果你有一个未知数 TestId值，那么您将需要使用动态 SQL 在运行时生成列列表。您的代码将是:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(testId) 
                    from tests
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colNames = STUFF((SELECT distinct ',' + QUOTENAME(testId) +' as Test'+cast(testId as varchar(10))
                    from tests
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Name, age, ' + @colnames + ' from 
             (
                select P.Name, P.Age, t.TestID, t.Result 
                from tests t
                inner join person P 
                  on p.ID = t.PersonID
            ) x
            pivot 
            (
                sum(Result)
                for TestID in (' + @cols + ')
            ) p '

execute(@query)

见 SQL Fiddle with Demo .

它们都会产生相同的结果，不同之处在于如果测试 id 的数量发生变化，动态的会增加/减少列:

| NAME | AGE | TEST1 | TEST2 | TEST3 |
--------------------------------------
| Jack |  25 |   125 |   120 |    75 |
| Jill |  23 |    90 |    95 |   7.2 |