Our database stores vehicle data in one table, but 3 different types of data are stored in the one table. The table contains all the columns for all 3 types so when you query the table you get at least 3 rows back with null values for all the columns that don't apply to that record. The data is imported to the table when it's updates so there's a possibility that they're updated at different times so they have a different BATCH like: BATCH TYPE ID RATING INSURANCE SAFETY 300 SAFE 123 NULL NULL A 300 INS 123 NULL YES NULL 250 RATE 123 A NULL NULL
What I'd like returned is: ID, RATING, INSURANCE, SAFETY 123 A YES A
I'm trying to do a case statement to pull the data down, but I keep ending up with multiple rows because of all the nulls. I tried doing a SUM of the case statement with an ISNULL(SAFETY,0) but I can't SUM char values.
I can probably do this with 3 temp tables to load the data that I want for each TYPE into them and then select and join them together, but is there a better way to do this?
if you want additional details to be shown that you need something like below
MAX(CASE WHEN TYPE = 'RATE' THEN RATING END) AS RATING,
MAX(CASE WHEN TYPE = 'INS' THEN INSURANCE END) AS INSURANCE,
MAX(CASE WHEN TYPE = 'SAFE' THEN SAFETY END) AS SAFETY
GROUP BY ID