Bring a row into a column

I have the following table as my input:

Date   | Agent Name | Agent Department | Call ID | Transfer? | TransferToAgent_Dept    
---------------------------------------------------------------------------------
1/1/20 | ABC        | Sales            | 121     | Yes       | Marketing
1/1/20 | XYZ        | Marketing        | 121     | No        | NULL

The output that I am looking for is:

Date   | Agent Name | Agent Department | Call ID | Transfer? | TransferToAgent | TransferToAgent_Dept
---------------------------------------------------------------------------------------------------
1/1/20 | ABC        | Sales            | 121     | Yes       | XYZ             | Marketing

So the Agent name that currently appears in the row below, I want to bring it up as a column.

Answer

Do a self join

select 
    a.Date, a.[Agent Name], a.[Agent Department], a.[Call ID], a.[Transfer?], 
    b.[Agent Name] as TransferToAgent, 
    a.TransferToAgent_Dept    
from tbl a
inner join tbl b on a.[Call ID] = b.[Call ID] and a.[Transfer?] = 'Yes'
    and b.[Transfer?] = 'No'

Advertisement

Answer