要按时间顺序透视的多个值

Question

我希望在如何思考和排序这个SQL问题的帮助，不一定与实际的代码。

我有一个很大的背景调查表格，我的字段是：

[First Name]
[Last Name]
[SSN]
[Administration]    // Who conducted the background check
[Date of Birth]     // This field is often null
[Result]            // Possible values are 'No Record', 'Record', or Null
[ResultDate] 

大多数人都有不止一次背景调查。例如，John Doe可能在2011年1月1日由管理部门X提供一个背景，在2012年1月1日由管理部门Y提供另一个背景。

我想按时间顺序将结果、ResultDate和管理放在一起。例如，记录将为

John Doe - 'Record' - 1/1/2011 - Administration X - 'Record' on 1/1/2013

我似乎想不出该怎么做。您将如何基于三个不同的字段进行透视？如果您可以基于三个不同的字段(结果、ResultDate、管理)进行透视，那么您可以基于ResultDate按时间顺序对它们进行排序吗？

Answer 1

您可以使用row_number按名称和SSN对结果日期进行排序，并将结果、结果日期和管理列连接起来：

-- load sample data
if object_id('tempdb..#background_checks','u') is not null
    drop table #background_checks
create table #background_checks
    (
    [First Name] varchar(50),
    [Last Name] varchar(50),
    [SSN] char(11),
    [Administration] varchar(50),
    [Result] varchar(50),
    [Result Date] date
    )
insert into #background_checks
    values
        ('John','Doe',NULL,'Administration X','Record','2011-01-01'),
        ('John','Doe',NULL,'Administration Y','Record','2012-01-01'),
        ('John','Doe',NULL,'Administration Z','No Record','2013-01-01')

-- pivot results
select
    [First Name],
    [Last Name],
    [1] as [Result 1],
    [2] as [Result 2],
    [3] as [Result 3]
from (select
        [First Name],
        [Last Name],
        [Result] + ' - ' +
            convert(varchar(10),[Result Date],101) + ' - ' +
            [Administration] as [Result],
        row_number() over(partition by [First Name],[Last Name],[SSN]
            order by [Result Date]) as ColumnID
      from #background_checks) t
      pivot (
            max([Result]) for ColumnID in([1],[2],[3])
            ) p1