SQL Query Help

SQL 201 Reputation points
2021-04-05T16:55:15.527+00:00

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:
84531-output.png

No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 68,091 Reputation points Microsoft MVP
    2021-04-05T17:27:33.027+00:00

    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
    
    No comments

2 additional answers

Sort by: Most helpful
  1. Viorel 82,746 Reputation points
    2021-04-05T17:21:36.247+00:00

    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
    
    No comments

  2. MelissaMa-MSFT 24,121 Reputation points Microsoft Employee
    2021-04-06T02:37:33.07+00:00

    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.

    No comments