I have a KVP Key value pair Table, Need sql to make it relational structure...!

I have a KVP Table and the structure is ID, Key,开发者_开发百科 Value.

Here are some example values...

ID, KEY,       VALUE
---------------------
 1, STATUS,    TRUE
 1, AGE GROUP, 10
 1, TRAVEL,    Y
 2, STATUS,    FALSE
 2, AGE GROUP, 20
 2, TRAVEL,    N

I want these values to be transformed as below...

ID, STATUS, AGE GROUP, TRAVEL
------------------------------
 1, TRUE,   10,        Y
 2, FALSE,  20,        N

I have read about crosstab/pivot, but I'm not able to make a query which can give me the above output. Is there any way in SQL to make my output look like the above example?

Assuming Oracle based on the tag, then

SELECT DISTINCT id
  , MAX(CASE key WHEN 'STATUS' THEN value ELSE null END) OVER (PARTITION BY id) status
  , MAX(CASE key WHEN 'AGE GROUP' THEN value ELSE null END) OVER (PARTITION BY id) age_grp
  , MAX(CASE key WHEN 'TRAVEL' THEN value ELSE null END) OVER (PARTITION BY id) travel
FROM kvp
ORDER BY id;

There are probably optimizations, but this works (tested in SQLite and MySQL)

select stat.id as id, status, age_group, travel from 
(select id, Value as status from kvp
where Key = 'STATUS') as stat JOIN
(select id, Value as age_group from kvp
where Key = 'AGE GROUP') as age on stat.id = age.id JOIN
(select id, Value as travel from kvp
where Key = 'TRAVEL') as trav on stat.id = trav.id;

Edited to fix a possible ambiguity.

select
    min(decode(KEY, 'ID',        VALUE, null)) ID,
    min(decode(KEY, 'STATUS',    VALUE, null)) STATUS,
    min(decode(KEY, 'AGE GROUP', VALUE, null)) AGE_GROUP,
    min(decode(KEY, 'TRAVEL',    VALUE, null)) TRAVEL
from
    KVP