Making 1 row from 2

• SQLHeap

  Hall of Fame

  Points: 3572

  More actions

  October 1, 2020 at 3:14 pm

  Go to Answer

  #3792278

  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: 246960

  More actions

  October 1, 2020 at 3:22 pm

  Answer

  #3792280

  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;

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• SQLHeap

  Hall of Fame

  Points: 3572

  More actions

  October 1, 2020 at 3:25 pm

  #3792281

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

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