question

SQL-4608 avatar image
0 Votes"
SQL-4608 asked MelissaMa-msft answered

SQL Query Help

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


sql-server-transact-sql
output.png (3.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @SQL-4608,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.