Flatten a Table in SQL with dynamic column name

Question

I have following table MyTable

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

  1   | abc |123  |abc@gmail.com    |null    |  NULL        |Primary
  1   | kbd |133  |kbd@gmail.com    |A       |  Address1    |Local

I am able to flatten the above talble by using following query:

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         |abc@gmail.com| kbd         |kbd@gmail.com  |A          | Address1

But problem comes when I have same TypeOfContact multiple times as shown below:

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

  1   | abc |123  |abc@gmail.com    |null    |  NULL        |Primary
  1   | kbd |133  |kbd@gmail.com    |A       |  Address1    |Local
  1   | vgk |999  | vgk@gmail.com   |B       |  Address2    |Local

If I use following query then I will get 2 records

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         |abc@gmail.com| kbd         |kbd@gmail.com  |A          | Address1  
abc         |abc@gmail.com| vgk         |vgk@gmail.com  |B          | Address2

Instead of above transformation I need dynamic columns so that i will get only 1 resultset like LocalName1, LocalName2, LocalEmail1, LocalEmail2, LocalAddress2 etc as follows:

Required DataFormat:

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

abc         |abc@gmail.com| kbd         |kbd@gmail.com  |A              | Address1      |vgk       |vgk@gmail.com       |        B      | Address2

Table formatting is problematic. Please take the time to make the tables readable. — nicomp, Nov 24 '20 at 11:27
@nicomp I have added a screenshot.. Hope now the tables are readable — Aathira, Nov 24 '20 at 11:49
@Aathira . . . Do you know the columns you want in the result table? If you do not, then you need to use dynamic SQL. — Gordon Linoff, Nov 24 '20 at 12:48
@GordonLinoff I don't know the column names so as you said dynamic SQL will be applicable here — Aathira, Nov 24 '20 at 12:50

Flatten a Table in SQL with dynamic column name

0 Answers0