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.
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