SQL "transpose" question

Hi, all. Situation: ------------------------------------------------------------------ create table source(ppl_id integer, code char(2)); insert into source (ppl_id, code) values (1,'10'); insert into source (ppl_id, code) values (1,'20'); create table target (ppl_id integer, codes(20));------------------------------------------------------------------ So, in source table I have two rows: 1, '10' 1, '20' In target I need one row: 1, '10, 20'. I have to collect all codes for given ppl_id and form a string with ", " as delimiter. Delimiter part is easy, but how to select data in needed way?

select ppl_id, multiset(select item inner_s.code from source inner_s where inner_s.ppl_id = outer_s.ppl_id)::lvarchar, replace(replace(substr(multiset(select item inner_s.code from source inner_s where inner_s.ppl_id = outer_s.ppl_id)::lvarchar, 10), "}"),"'") from source outer_s ; Also I assumed that codes(20) was really codes lvarchar or varchar(20).

select distinct s1.ppl_id, replace( replace( replace( replace( replace(multiset(select item code from source s2 where s1.ppl_id = s2.ppl_id)::lvarchar, '''', ''), -- del the string delims 'MULTISET{', ''''), -- repl MULTISET prefix with stringdelim ' ', ''), -- del spaces '}', ''''), -- repl MS postfix with string delim ',', ', ') as codes -- add space after comma from source s1 where s1.ppl_id = 1 -- filter kasis_100@yahoo.com schrieb: > Hi, all. > > Situation: > ------------------------------------------------------------------ > > > create table source(ppl_id integer, code char(2)); > insert into source (ppl_id, code) values (1,'10'); > insert into source (ppl_id, code) values (1,'20');> > > create table target (ppl_id integer, codes(20));> ------------------------------------------------------------------ > So, in source table I have two rows: > 1, '10' > 1, '20' > > > In target I need one row: 1, '10, 20'. > > > I have to collect all codes for given ppl_id and form a string with ", > " as delimiter. > Delimiter part is easy, but how to select data in needed way? > >