Multiple data types in one table with nulls

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple data types in one table with nulls

Author

Topic

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2013-09-25 : 12:29:52

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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-25 : 13:09:07

For your sample data, the following should work.

SELECT
	ID, 
	MAX(Rating) AS Rating,
	MAX(insurance) AS Insurance,
	MAX(SAFETY) AS SAFETY
FROM
	YourTable
GROUP BY
	Id;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-28 : 04:02:59

if you want additional details to be shown that you need something like below


SELECT  ID, 
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
FROM TableName
GROUP BY ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources