SQL:键值:列中的值？

Question

我的表格是这样的:

+--------------------------+
| key | value-name | value |
+-----+------------+-------+
| 1   | color      | green |
| 1   | height     | 15    |
| 2   | whatever   | lol   |
+-----+------------+-------+

我想把它翻转成这样:

+-----+-------+--------+----------+
| key | color | height | whatever |
+-----+-------+--------+----------+
| 1   | green | 15     | ---      |
| 2   | ---   | ---    | lol      |
+-----+-------+--------+----------+

像这样的sql:

SELECT key AS k,
  (SELECT color FROM table WHERE key = k),
  (SELECT height FROM table WHERE key = k), ...

...等等

如何在不为每个值名称创建子选择的情况下完成(因为我相信这不是很快)？

我的 DBS 是 Oracle 10g。

编辑:后来我找到了一些例子，但他们都只是加入，我不能这样做，因为我的表有很多值名称。

Answer 1

select 
  key
, max( case value-name when 'color' then value else NULL end)      as color
, max( case value-name when 'heght' then value else NULL end)      as height
, max( case value-name when 'whatever' then value else NULL end)   as whatever
from 
  table
group by key