Post reply

SQL Query Help - Without using Pivot

  • April 5, 2021 at 5:31 pm

    #3865670

    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')

    EXPECTED OUTPUT:

    output

     

    Is there a way to do this without the Pivot?

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

     

    Thanks!

  • April 5, 2021 at 5:33 pm

    #3865675

    Nevermind... I got it...

    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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply