Making 1 row from 2

  • SQLHeap

    Hall of Fame

    Points: 3567

    I'm struggling a bit here. It seems easy, but I 'm not getting it.

    I'm trying to make 1 row from 2 rows where if 1 row has no data and the second one does, then get the distinct row with all the data. Kind of a merge.

    My example table with 2 rows of different values, same ID.

    CREATE TABLE #Temp (ID INT, LegalName VARCHAR(25), City VARCHAR(15), Zip CHAR(5))

    INSERT INTO #temp
    VALUES (184382, 'Insurance Agency', 'Redding', ''), (184382, 'Insurance Agency', '', '96002')

     

    How do I get 1 row with all the values?

    There is an exception to every rule, except this one...

  • Phil Parkin

    SSC Guru

    Points: 244792

    You can use MAX to make this work:

    SELECT t.ID
    ,MAX(t.LegalName)
    ,MAX(t.City)
    ,MAX(t.Zip)
    FROM #Temp t
    GROUP BY t.ID;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • SQLHeap

    Hall of Fame

    Points: 3567

    Thanks, I knew I was making it way harder than it had to be.

    There is an exception to every rule, except this one...

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

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