SQL Server - 将行转移到列
问题描述:
我们在MSSQL数据库中有一张表,用于存储Office Person关系数据。表结构是SQL Server - 将行转移到列
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
如何形成查询以获取此格式的数据?
问候,
约翰
答
使用条件汇总:
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
rextester演示:http://rextester.com/OTFBIS66929
[将行转换为MS SQL中的列]的可能重复(https://*.com/questions/35451934/convert-rows-to-columns-in-ms-sql) –