Oracle SQL -- Grabbing values from multiple rows

Question

I have a table like this:

TABLE: FACTS
ID       KEY         VALUE
1        name        Jeremy
1        height      5'11
1        awesomeness 10
2        name        Mark
2        awesomeness 4
3        height      4'6

So, the (ID,KEY) tuple can be considered as a primary key.

I am trying to return rows like this:

ID     NAME     HEIGHT    AWESOMENESS
1      Jeremy   5'11      10
2      Mark     (null)    4
3      (null)   4'6       (null)

So other than by doing a sub-select for each column, how can grab the key values, if they are there, and collect them into my single row? What I tried so far was:

SELECT 
  id,
  CASE WHEN facts.key = 'name' THEN value END name,
  CASE WHEN facts.key = 'height' THEN value END height,
  CASE when facts.key = 'awesomeness' THEN value END awesomeness
FROM
  facts
WHERE
  facts.id in (1,2,3)

But for obvious reasons this returns one row per key that matches, not one row per id.

How can I go about getting this the way I want?

Thanks!

Justin Cave · Accepted Answer · 2012-04-26 01:44:51Z

5

You can pivot the data like this in any version of Oracle.

SELECT id,
       MAX( CASE WHEN key = 'name' THEN value ELSE null END ) name,
       MAX( CASE WHEN key = 'height' THEN value ELSE null END ) height,
       MAX( CASE WHEN key = 'awesomeness' THEN value ELSE null END ) awesomeness
  FROM facts
 WHERE id IN (1,2,3)
 GROUP BY id

If you're using 11g, you could also use the PVOT operator.

If this is representative of your data model, though, that sort of entity-attribute data model is generally going to be rather inefficient. You would generally be much better served with a table that had columns for name, height, awesomeness, etc.

answered Apr 26, 2012 at 1:44

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

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

3 Comments

Michael Berkowski Over a year ago

10s ahead of me, and with a slightly tidier version of the same query :/

Jeremy Over a year ago

This is great and works perfectly, but I don't get it. What is the logic here? Why max, say, instead of min? (P.S. this table backs a system where users can create arbitrary keys so it's necessary to have it backed by something like this)

Justin Cave Over a year ago

@Jeremy - Any aggregate function which returns the non-NULL value if there are multiple rowsonly one of which is non-NULL would be sufficient. MIN should work just as well-- it's just conventional to choose MAX

Collectives™ on Stack Overflow

Oracle SQL -- Grabbing values from multiple rows

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related