Re: How to get normalized data from tekst column

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