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
– Eilidh, Commented Dec 18, 2014 at 17:50

Mureinik · Accepted Answer · 2014-12-18 17:49:37Z

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, 2014 at 17:49

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

O.D.P Over a year ago

That did the trick! I didn't know about max() on strings. Thanks a bunch!

user4002899 · Accepted Answer · 2014-12-18 18:33:44Z

1

    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

answered Dec 18, 2014 at 18:33

user4002899

851 silver badge18 bronze badges

Comments

Timo D · Accepted Answer · 2014-12-18 17:52:00Z

0

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

answered Dec 18, 2014 at 17:52

Timo D

1,81311 silver badges17 bronze badges

1 Comment

O.D.P Over a year ago

The above poster got there first, but thanks for the help! I did not know that about coalesce.

Collectives™ on Stack Overflow

SQL - How to combine rows based on unique values

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related