SQL Server语言 - 将行转置为列

Question

我们的MSSQL数据库中有一个表，用于存储办公室人员关系船数据。表结构为

CREATE TABLE (
    OfficeID UNIQUEIDENTIFIER
    PersonID UNIQUEIDENTIFIER
    Designation VARCHAR(20)
)

该表将具有值

    E1F5ECA9-7D5A-4580-AA7D-0003A582F286|E1F5ECA9-7D5A-3580-AA7D-0003A582F286|Manager
    E1F5ECA9-7D5A-4580-AA7D-0003A582F286|E1F5ECA9-7D5A-3580-AA7D-0003A582F286|Engineer
    E2F5ECA9-7D5A-4580-AA7D-0003A582F286|E2F5ECA9-7D5A-3580-AA7D-0003A582F286|Manager
    E2F5ECA9-7D5A-4580-AA7D-0003A582F286|E2F5ECA9-7D5A-3580-AA7D-0003A582F286|Engineer

我想得到的是每个办公室只能有一个经理和一个工程师。我期待的输出是

OfficeID|Manager ID| Engineer ID|
E1F5ECA9-7D5A-4580-AA7D-0003A582F286|E1F5ECA9-7D5A-3580-AA7D-0003A582F286| E1F5ECA9-7D5A-3580-AA7D-0003A582F286
E2F5ECA9-7D5A-4580-AA7D-0003A582F286|E2F5ECA9-7D5A-3580-AA7D-0003A582F286|E2F5ECA9-7D5A-3580-AA7D-0003A582F286

如何形成查询以获取此格式的数据？

问候

John

Answer 1

使用

条件聚合：

select 
    OfficeId
  , ManagerId  = max(case when Designation = 'Manager'  then PersonId end)
  , EngineerId = max(case when Designation = 'Engineer' then PersonId end)
from t
group by OfficeId

或使用pivot()：

select
    OfficeId
  , ManagerId = Manager
  , EngineerId = Engineer
from t
pivot (max(PersonId) for Designation in ([Manager],[Engineer])) p

RexTest演示：http://rextester.com/OTFBIS66929