SQL - How to combine rows based on unique values

Question

The table below was created from another table with columns ID,Name,Organ,and Age. The values found in the Organ column were codes which designated both organ and condition.

Using CASE I made a table like this:

--------------------------------------------------------
ID      NAME        Heart   Brain   Lungs   Kidneys AGE
1318    Joe Smith   NULL    NULL    NULL    NULL    50
1318    Joe Smith   NULL    NULL    NULL    NULL    50
1318    Joe Smith   NULL    NULL    NULL    Below   50
1318    Joe Smith   NULL    NULL    NULL    Below   50
1318    Joe Smith   NULL    NULL    Above   NULL    50
1318    Joe Smith   NULL    NULL    Above   NULL    50
1318    Joe Smith   Average NULL    NULL    NULL    50
1318    Joe Smith   Average NULL    NULL    NULL    50
--------------------------------------------------------

I would like to query this table and get the following result:

--------------------------------------------------------
1318    Joe Smith   Average NULL    Above   Below   50   
--------------------------------------------------------

In other words I would like to create one record based on the unique values from each column.

Hmmm... I'd say you might want to go back to your original tables and do something from them, rather than basing your query off this intermediary step - it seems clumsy. — Eilidh, Dec 18 '14 at 17:50

score 5 · Accepted Answer · answered Dec 18 '14 at 17:49

5

Assuming each organ can either have just one value or a null, as shown in the sample data, the max aggregate function should do the trick:

SELECT   id, name, 
         MAX(heart), MAX(brain), MAX(lungs), MAX(kidneys), 
         age
FROM     my_table
GORUP BY id, name, age

answered Dec 18 '14 at 17:49

Mureinik

297,002
52
306
350

That did the trick! I didn't know about max() on strings. Thanks a bunch! – O.D.P Dec 18 '14 at 17:59

score 1 · Answer 2 · answered Dec 18 '14 at 18:33

    select id,name,heart=(select distinct(heart) from organ where id=1318 and heart is not null)
    ,brain= (select distinct(brain) from organ where id=1318 and brain is not null)
    ,lungs=(select distinct(lungs) from organ where id=1318 and lungs is not null)
    ,kidneys = (select distinct(kidneys) from organ where id=1318 and kidneys is not null)
    ,age from organ where id=1318

score 0 · Answer 3 · answered Dec 18 '14 at 17:52

What you want to do here is aggregate your results by ID and NAME. This means that there will only be one row for each unique (ID, NAME) pair. This can be achieved with the GROUP BY keyword.

Now, depending on the Database you are using (MySQL, DB2, ...) this query might look a bit different, but you could try this one:

SELECT 
   ID, NAME, 
   MAX(Heart), MAX(Brain), MAX(Lungs), MAX(Kidneys), MAX(AGE) 
FROM MYTABLE GROUP BY ID, NAME

This will give you the "maximum" value of each column. Like I said, depending on your Database this might not work with string columns, so you could also try COALESCE, which gives you the first NOT NULL value of a list:

SELECT 
   ID, NAME, 
   COALESCE(Heart), COALESCE(Brain), COALESCE(Lungs), COALESCE(Kidneys), COALESCE(AGE) 
FROM MYTABLE GROUP BY ID, NAME

The above poster got there first, but thanks for the help! I did not know that about coalesce. — O.D.P, Dec 18 '14 at 18:00

SQL - How to combine rows based on unique values

3 Answers3

Linked