SQL Case Statement get rid of nulls

Question

I have data across two tables bound by an ID. The problem is I do not want to select the column names but the actual data inside. So I decided to use a SELECT CASE statement but it produces a table with many nulls. How would I just select the data that isn't null?

column value   column name
200              car
300              boat

SQL QUERY:

SELECT 
CASE WHEN  b.name = 'car' THEN a.value END as 'car',
CASE WHEN  b.boat= 'car' THEN a.value END as 'boat' 
WHERE a.id = b.id

The output is

CAR  BOAT
200  NULL
NULL 300

How would I only select none null values?

http://stackoverflow.com/questions/5658457/not-equal-operator-in-t-sql-on-null — Jodrell, Jul 03 '12 at 15:52

score 2 · Accepted Answer · answered Jul 03 '12 at 15:52

2

GROUP BY with MAX for the other columns:

SELECT a.id,
       MAX(CASE WHEN  b.name = 'car' THEN a.value END) as 'car',
       MAX(CASE WHEN  b.boat= 'car' THEN a.value END) as 'boat' 
WHERE a.id = b.id   
GROUP BY a.id

answered Jul 03 '12 at 15:52

Mahmoud Gamal

78,257
17
139
164

SQL Case Statement get rid of nulls

1 Answers1