Oracle SQL -- 从多行中获取值

2022-01-22 00:00:00 pivot sql oracle dynamic-sql

我有一张这样的桌子:

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) 元组可以被视为主键.

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)

但由于显而易见的原因，这会为每个匹配的键返回一行，而不是每个 id 一行.

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?

谢谢！

推荐答案

您可以在任何版本的 Oracle 中像这样对数据进行透视.

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

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

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

但是，如果这代表您的数据模型，那么这种实体属性数据模型通常效率会很低.通常，您最好使用包含 name、height、awesomeness 等列的表格.

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.

相关文章