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.
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 SAFETY300 SAFE 123 NULL NULL A300 INS 123 NULL YES NULL250 RATE 123 A NULL NULLWhat I'd like returned is:ID, RATING, INSURANCE, SAFETY123 A YES AI'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 SAFETYFROM YourTableGROUP 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 belowSELECT 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 SAFETYFROM TableNameGROUP BY ID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|