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

Question

I have a KVP Table and the structure is ID, Key, Value and below are the sample values....

Table with 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 date to transform as below (Output)

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

I have read about crosstab/pivot - but not able to make a query which can give me the above output. The table structure cant be changed...! My bad.

Is there any way in sql, to make my output look like above ?

Answer 1

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.

Answer 2

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;

Answer 3

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