如何从包含 Oracle 中键值对结构的表中获取值

Question

我有一个结构如下的表，我想根据id值获取名称和地址值。

我尝试使用以下查询，但它不起作用。

select name,address from details where c_id=111
table name=**details**
c_id   key    value
111    name   abc
111    email  [e-mail]
111    address  h.no 22,hyderabad
222    name    raj
222    email   [e-mail]
222    address h.no 24,india

Answer 1

可以使用子查询获取名称和地址的结果集，然后联接它们：

SELECT name, address
FROM   (SELECT c_id, value AS name
        FROM   details
        WHERE  key = 'name') n
JOIN   (SELECT c_id, value AS address
        FROM   details
        WHERE  key = 'address') a ON n.c_id = a.c_id