0

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
  • I am Using MS SQL Server. It works when I filter down to looking for 1 specific UnitID, but not when I run it Wide Open. I get a bunch of NULLS. – Jordon Griffith Feb 7 '18 at 18:49
  • Why do you have multiple rows per UnitID? Is there another ID field which makes each row unique? – James Feb 8 '18 at 13:44
  • Yes, there are unique row ID's. The unique ID field would be MiscPromptUnitID. – Jordon Griffith Feb 8 '18 at 16:35
1

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.

  • Well, it works when I filter down to looking for 1 specific UnitID, but not when I run it Wide Open. I get a bunch of NULLS. – Jordon Griffith Feb 7 '18 at 18:33
  • Are you sure that isn't supported by your data? you should get NULLs for UnitIDs where all the values in the column in question are NULL. Check the underlying data for the summary rows that are showing NULLs. – RDFozz Feb 7 '18 at 19:39
0

Your question is a little bit confusing, but I think what you need is:

select unitId, max(col1), max(col2), max(col3)
from table1
group by unitId

Substitute your own column names for the dummies I have used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.