SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple data types in one table with nulls
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ITTrucker
Yak Posting Veteran

USA
52 Posts

Posted - 09/25/2013 :  12:29:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 09/25/2013 :  13:09:07  Show Profile  Reply with Quote
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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/28/2013 :  04:02:59  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000