Re: [sqlite] Rows to columns

[Dennis Cote]

Matthias Teege wrote:

Moin,
I have a table with a lot of field/value pairs and I need to create a
view where rows are columns. Somthing like that:

field|value
name | ab
stre | yo
nr   | 12

I need

name | stre | nr
ab   | yo   | 12

Is this possible with sqlite3?

Many thanks
Matthias

 

Matthias,

You can do this if you have another column in the pairs table that 
identifies which set of pairs belong together on one row in the column 
table. The SQL script below demonstrates what you need after adding the 
id column to your pair data.

create table pairs (id, field, value);
create table cols (id, name, stre, nr);

insert into pairs values(1, 'name', 'ab');
insert into pairs values(1, 'stre', 'yo');
insert into pairs values(1, 'nr', '12');
insert into pairs values(2, 'name', 'bc');
insert into pairs values(2, 'stre', 'xy');
insert into pairs values(2, 'nr', '13');

  
insert into cols
   select name.id, name.value, stre.value, nr.value
   from pairs as name
   join pairs as stre on (name.id = stre.id)
   join pairs as nr on (name.id = nr.id)
   where name.field = 'name'
   and stre.field = 'stre'
   and nr.field = 'nr';
  
select * from cols;
1|ab|yo|12
2|bc|xy|13

HTH
Dennis Cote