Oracle SQL-从多行中获取值 [英] Oracle SQL -- Grabbing values from multiple rows

问题描述

我有一个这样的表:

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

因此，(ID，KEY)元组可以视为主键.

我试图返回这样的行:

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

除了对每列进行子选择之外，如何抓住键值(如果有)并将它们收集到我的单行中呢?到目前为止，我尝试过的是:

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)

但是出于明显的原因，每个匹配的键返回一行，而不是每个id返回一行.

我该如何以自己想要的方式得到这个?

谢谢！

解决方案

您可以在任何版本的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

如果您使用的是11g，则还可以使用PVOT运算符.

但是，如果这代表您的数据模型，则这种实体属性数据模型通常效率会很低.通常，使用具有name，height，awesomeness等列的表的表会更好.

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!

解决方案

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.

这篇关于Oracle SQL-从多行中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！