sql - 使用动态列名在 SQL 中展平表

Question

我有下表 MyTable

RequestId| Name|Phone|  ContactEmail    |RoleType|  Address     |TypeOfContact

  1   | abc |123  |[e-mail]    |null    |  NULL        |Primary
  1   | kbd |133  |[e-mail]    |A       |  Address1    |Local

我可以通过使用以下查询来展平上述表格：

SELECT a.Name as PrimaryName, a.ContactEmail as PrimaryEmail
,b.Name as LocalName,b.ContactEmail as LocalEmail
,b.Address as LocalAddress, b.RoleType as LocalRoleType
FROM  MyTable a   join   MyTable on a.requestid=b.requestid 
WHERE    a.requestid=1 AND a.TypeOfContact='Primary' and b.TypeofContact='Local'


PrimaryName |PrimaryEmail | LocalName   | LocalEmail    |LocalRoleType  |LocalAddress   

abc         |[e-mail]| kbd         |[e-mail]  |A          | Address1     

但是当我多次使用相同的 TypeOfContact 时，问题就来了，如下所示：

 RequestId| Name|Phone| ContactEmail    |RoleType|  Address     |TypeOfContact

  1   | abc |123  |[e-mail]    |null    |  NULL        |Primary
  1   | kbd |133  |[e-mail]    |A       |  Address1    |Local
  1   | vgk |999  | [e-mail]   |B       |  Address2    |Local

如果我使用以下查询，那么我将获得 2 条记录

SELECT a.Name as PrimaryName, a.ContactEmail as PrimaryEmail,b.Name as LocalName,b.ContactEmail as 
LocalEmail1
,b.Address as LocalAddress, b.RoleType as LocalRoleType
FROM  MyTable a   join   MyTableb on a.requestid=b.requestid 
WHERE    a.requestid=1 AND a.TypeofContact='Primary' and b.TypeofContact='Local'

PrimaryName |PrimaryEmail | LocalName   |LocalEmail |LocalRoleType  |LocalAddress   

abc         |[e-mail]| kbd         |[e-mail]  |A          | Address1  
abc         |[e-mail]| vgk         |[e-mail]  |B          | Address2  

我需要动态列而不是上述转换，这样我只会得到 1 个结果集，如 LocalName1、LocalName2、LocalEmail1、LocalEmail2、LocalAddress2 等，如下所示：

所需数据格式：

PrimaryName |PrimaryEmail | LocalName1  |LocalEmail1    |LocalRoleType1 |LocalAddress1  |LocalName2|LocalContactEmail2  |LocalRoleType2 |LocalAddress2

abc         |[e-mail]| kbd         |[e-mail]  |A              | Address1      |vgk       |[e-mail]       |        B      | Address2

标签： sqlsql-serverrdbms