Sql-server – How to select TOP 1 value from each column where value is not null

coalescesql servertop

I am trying to select many columns from a table where there can be multiple rows with the same ID. Each row contains data from columns that I want to return in one row. Any time a column has a value other than NULL, I want to show that. If all values in that column are NULL, it is OK to show NULL.

Example)

SELECT * FROM Table1 WHERE UnitID='73355'

This returns multiple rows. Each row contains data in columns that I want to merge together. I don't want to keep my WHERE clause. In this example I am just using it to narrow down my results for troubleshooting purposes. I want every row to return in the table but merge the data where the UnitID's match. There could be 1, 2, 3, or more rows returned with matching UnitID's. Each Row will have data in it I will want merged into 1 row.

I tried TOP 1 but it only returns the first row. I want TOP 1 but each column I want the value that is NOT NULL.

I have this now…If i put in my WHERE clause a specific UnitID, it works. However, I want to put other columns in my WHERE clause and remove UnitID. I want to filter based off LRUnitStatus and Model. As soon as I do that, it does not work.

    SELECT UnitID, 
           MAX(LRUnitStatus) as 'Status', 
           MAX([UnitNumber]) as 'Unit #', 
           MAX(Make) as Make, MAX(Model) as Model, 
           MAX([ProductionYear]) as 'Year', 
           MAX(Length) as 'Length', 
           MIN([Rear Door Type]) as 'Rear Door', 
           MAX([Suspension Type]) as 'Suspension', 
           MAX([LocationBranchCode]) as 'Location', 
           MAX(HUBO) as 'HUBO',
           MIN([Wheel Type]) as 'Wheels', 
           MAX([Tire Size]) as 'Tires', 
           MAX([Floor Type]) as 'Floor', 
           MAX([Tire Inflation]) as 'Tire Inflation', 
           MAX([Side Skirts]) as 'Skirts', 
           MAX([Side Panels]) as 'Panels', 
           MAX([Roof Type]) as 'Roof', 
           MAX([Logistic Posts]) as 'Log. Posts',
           MAX([E-Track]) as 'ETrack', 
           MAX([Axle Spread]) as 'Axle'
    FROM   [ProfitMaster].[dbo].[vwUKTS_LR_jtg] LR
    WHERE  UnitID='73316'
    GROUP BY UnitID

Best Answer

What about:

SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ...
  FROM Table1
 GROUP BY UnitID
;

This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found.

Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there may be other possibilities as well.

Here's a SQLFiddle (against SQL Server - the basic command should work in just about any SQL language) showing it in action. You can see that it picks values from different rows if needed. Even if only one out of five rows has a non-NULL value, both MAX and MIN will return that one non-NULL value. You only get NULL if there are no non-NULL values at all for a given UnitID.

Related Question