SQL Query Help

Question

Need help with query. I want the output to be display in single row. We have 2 rows for each ID for Address Type and I want the Home and Mailing address city display in single row.

create table #Test  
(ID int,  
 HomeCity varchar(10),  
 AddrType varchar(10))  
  
 insert into #Test values (10,'Phoenix','Home')  
 insert into #Test values (10,'Boston','Mailing')  
 insert into #Test values (20,'Memphis','Home')  
 insert into #Test values (20,'Durham','Mailing')  
 insert into #Test values (30,'New York','Mailing')  
  
  
 SELECT * from #Test  

EXPECTED Output:

Answer 1

Here is a solution based on ANSI SQL, which does not use the proprietery PIVOT operator. While it is somewhat more verbose, I find this model easier to understand. It is also a lot easier to extend and adapt to different needs.

SELECT ID,
       MIN(CASE AddrType WHEN 'Home' THEN HomeCity END) AS HomeCity,
       MIN(CASE AddrType WHEN 'Mailing' THEN HomeCity END) AS MailingCity
FROM   #Test
GROUP  BY ID

Answer 2

One of solutions is:

select ID, isnull(Home, '') as HomeCity, IsNull(Mailing, '') as MailingCity
from #Test 
pivot
(
    max(HomeCity) for AddrType in ( Home, Mailing )
) p
order by ID

Answer 3

Hi @SQL ,

Glad that you already received your expected answer.

Please also refer blow dynamic pivot way from below:

declare @query nvarchar(max);  
declare @cols nvarchar(max);  
declare @cols1 nvarchar(max);  
  
select @cols = stuff((select ','+quotename(AddrType)  
         from #Test   
		 group by AddrType   
		 for xml path('')), 1, 1, '');  
  
select @cols1 = stuff((select ','+quotename(AddrType)+' '+AddrType+'City'  
         from #Test   
		 group by AddrType   
		 for xml path('')), 1, 1, '');  
  
set @query = 'select ID,'+@cols1+'  
       from #Test   
 pivot  
 (  
     max(HomeCity) for AddrType in (' +@cols+ ')  
 ) p  
 order by ID'  
  
exec sp_executesql @query;  

Output:

ID	HomeCity	MailingCity  
10	Phoenix	Boston  
20	Memphis	Durham  
30	NULL	New York  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.