[SOLVED] Oracle view creation from name value pair table

Oracle view creation from name value pair table

Folks,

I have a oracle database table which has name value pairs. A set of name value pairs is identified by row_num. Also set size may vary.

I want to create a view which converts set of name value pair into one row in view.

I hope my question is clear

Please let me know how to define this

I gave sample sql for table creation and inserts for you convenience

Thanks

create table name_value_pair (row_num varchar2(32), name varchar2(32),value varchar2(32));

insert into name_value_pair values ('row1', 'Col1', 'Val11');
insert into name_value_pair values ('row1', 'Col2', 'Val12');
insert into name_value_pair values ('row1', 'Col3', 'Val13');
insert into name_value_pair values ('row1', 'Col4', 'Val14');
insert into name_value_pair values ('row1', 'Col5', 'Val15');

insert into name_value_pair values ('row2', 'Col1', 'Val21');
insert into name_value_pair values ('row2', 'Col2', 'Val22');
insert into name_value_pair values ('row2', 'Col3', 'Val23');
insert into name_value_pair values ('row2', 'Col4', 'Val24');
insert into name_value_pair values ('row2', 'Col5', 'Val25');

insert into name_value_pair values ('row3', 'Col1', 'Val31');
insert into name_value_pair values ('row3', 'Col2', 'Val32');
insert into name_value_pair values ('row3', 'Col3', 'Val33');
insert into name_value_pair values ('row3', 'Col4', 'Val34');
insert into name_value_pair values ('row3', 'Col5', 'Val35');

insert into name_value_pair values ('row4', 'Col1', 'Val41');
insert into name_value_pair values ('row4', 'Col2', 'Val42');
insert into name_value_pair values ('row4', 'Col3', 'Val43');
insert into name_value_pair values ('row4', 'Col4', 'Val44');
insert into name_value_pair values ('row4', 'Col5', 'Val45');

insert into name_value_pair values ('row5', 'Col1', 'Val51');
insert into name_value_pair values ('row5', 'Col2', 'Val52');
insert into name_value_pair values ('row5', 'Col3', 'Val53');
insert into name_value_pair values ('row5', 'Col4', 'Val54');
insert into name_value_pair values ('row5', 'Col5', 'Val55');

commit;

Solution

you can use the query when creating the view:

select
  row_num, 
  max(decode(name, 'Col1', value)) col1, 
  max(decode(name, 'Col2', value)) col2,
  ...
from name_value_pair
group by row_num;

Perhaps you should reconsider the data model. This kind of data model sucks. Queries can't be optimized, hard to set constraints, and almost impossible to maintain data integrity.