On Thu, 17 Nov 2005, Dennis Cote wrote:
>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?
I have a library that does exactly this, except I'm still on SQLite 2.
>>
>>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
What is better is a view that dynamically returns rows based on the
underlying pairs table, rather than inserting into cols:
create view pairs_view as
select name.id, name.value, stre.value, nr.value
...
Also, you're better of changing the select slightly to name the returned
columns after the field being mapped:
...
select name.id, name.value as name, stre.value as stre, nr.value as nr
...
Finally, for that finishing touch, you can create "instead of" triggers on
the view, to change the underlying pairs table when operating on the view.
Something like:
create trigger pairs_view_insert instead of insert on pairs_view
begin
insert into pairs values ( id, 'name', new.name );
insert into pairs values ( id, 'stre', new.stre );
insert into pairs values ( id, 'nr', new.nr );
end;
Now you can work on the view almost exclusively, yet keep the flexibility
of the underlying pairs table.
My library generalises the pairs table into types and instances, such that
each pair (called fields in my library) has an associated type and
instance. The basic schema looks like:
create table fields (
type text,
instance text,
name text,
value text,
primary key (type,instance,name) on conflict replace
);
This way, a single table can contain multiple data types. The views and
triggers are built automatically, by using template rows (with empty
instance data to specify the fields required) to define the view. It's not
the most efficient schema, type and instance could be folded into a
single id such as the OP sample, but it keeps it simple.
Unfortunately, the library is not yet in a releasable state, as I have not
finalised it's capabilities, nor whether to generalise the DB interface to
accommodate other databases (it's tied to SQLite at the moment.) It is
also dependent on another C toolkit library which is similarly in a state
of flux at the moment.
If anyone shows interest, I can look at releasing the source in the near
future.
>
>HTH
>Dennis Cote
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
