Re: How to get normalized data from tekst column

On Nov 24, 2011, at 8:47, "Andrus" <kobruleht2(at)hot(dot)ee> wrote:

> Project table contains salesman names and percents as shown
> below. Single comment column contains 1-2 salesman names and commissions.
> How select normalized data from this table ?
>
> Andrus.
>
> CREATE TABLE project (
> id char(10) primary key,
> comment char(254)
> );
>
> insert into test values ('2010-12', 'Aavo 19%, Peedu 15%');
> insert into test values ('2010-22', 'Lauri-21%,Peedu 15%');
> insert into test values ('2011-33', 'Taavi 21%');
>
> How to create select statement in Postgresql 8.1.23 which
> selects this data as normalized table like
>
> CREATE TABLE commission (
> projectid char(10),
> salesman char(5),
> commission n(2) )
>
> result using data above should be
>
>
> '2010-12', 'Aavo', 19
> '2010-12', 'Peedu', 15
> '2010-22', 'Lauri', 21
> '2010-22', 'Peedu', 15
> '2011-33', 'Taavi', 21

Regular Expressions are your friend here. If you do not know them you should learn them; though if you ask nicely someone may just provide you the solution you need.

Split-to-array and unnest may work as well.

All this said, you are currently using an unsupported version of PostgreSQL and I do not know what specific functionality you have to work with.

David J.