Oninit cdi / Archive
Talk to an expert →

Re: Challenging : Aggregating several columns to single line

Posted in 2013

Topics: Stored Procedures & SPL, Security, Permissions & Auditing

Cesar Inacio Martins — — source: Usenet: comp.databases.informix

Hi Fernando, This is something what I try before , the problem is have all fields of same row togheter... where difficult use the data without include a treatment to separate each information. expression MULTISET{'1,1,foo ','2,1,bar ','3,1,baz '} expression MULTISET{'4,2,some ','5,2,random ','7,2,data '} expression MULTISET{'6,3,Data 1 ','8,3,Data 2 ','9,3,Data 3 '} 2013/3/13 Fernando Nunes <domusonline@gmail.com> > Food for thought: > > drop table teste; > create temp table teste ( id smallint, cat smallint, data char(10)); > insert into teste values ( 1, 1, 'foo ' ); > insert into teste values ( 2, 1, 'bar ' ); > insert into teste values ( 3, 1, 'baz ' ); > insert into teste values ( 4, 2, 'some ' ); > insert into teste values ( 5, 2, 'random ' ); > insert into teste values ( 6, 3, 'Data 1 ' ); > insert into teste values ( 7, 2, 'data ' ); > insert into teste values ( 8, 3, 'Data 2 ' ); > insert into teste values ( 9, 3, 'Data 3 ' ); > insert into teste values ( 10, 3, 'Data 4 ' );> > select * from teste;> select ms.* > from > ( > SELECT MULTISET( SELECT ITEM t.id || ',' || t.cat || ',' || t.data m1 > FROM teste t WHERE t.cat = tout.cat) FROM (SELECT unique cat from teste) > tout > ) ms > > > > > On Wed, Mar 13, 2013 at 12:01 AM, Cesar Inacio Martins < > cesar.inacio.martins@gmail.com> wrote: > >> >> This is something where I always see as huge challenge when need to play >> only with DML on Informix. >> I play now a little with this... and get no easy/simple solution, so >> far... >> >> Get this data : >> >> drop table teste; >> create temp table teste ( id smallint, cat smallint, data char(10)); >> insert into teste values ( 1, 1, 'foo ' ); >> insert into teste values ( 2, 1, 'bar ' ); >> insert into teste values ( 3, 1, 'baz ' ); >> insert into teste values ( 4, 2, 'some ' ); >> insert into teste values ( 5, 2, 'random ' ); >> insert into teste values ( 6, 3, 'Data 1 ' ); >> insert into teste values ( 7, 2, 'data ' ); >> insert into teste values ( 8, 3, 'Data 2 ' ); >> insert into teste values ( 9, 3, 'Data 3 ' );>> >> and transform into this : >> >> cat id1 data1 id2 data2 id3 data3 >> ----------------------------------------------------- >> 1 1 foo 2 bar 3 baz >> 2 4 some 5 random 7 data >> 3 6 Data 1 8 Data 2 9 Data 3 >> >> Where the logic is : aggregate into single line the 3 lines what have the >> same "cat" . >> This is possible on ifx 11.70 ? or 12.1? >> Only with DML... no SPL. (consider the user/system don't have grant to >> create procedure) >> >> Original question : >> http://stackoverflow.com/questions/15368750/aggregating-several-columns-to-single-colum >> >> Regards >> Cesar >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> >> > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... >

↪ replying to Cesar Inacio Martins
superboer7@t-online.de — — source: Usenet: comp.databases.informix

select a.*, b.* ,c.* from teste a, teste b , teste c where a.cat = b.cat and a.cat = c.cat and b.cat = c.cat and a.rowid != b.rowid and a.rowid != c.rowid and b.rowid != c.rowid and a.id < b.id and b.id < c.id order by a.cat; gives: id cat data id cat data id cat data 1 1 foo 2 1 bar 3 1 baz 4 2 some 5 2 random 7 2 data 6 3 Data 1 8 3 Data 2 9 3 Data 3 assume its what is required and yes rowids YUK... would be nice to have a PK on the table or a recordid...this will barf on a fragmented table... Superboer. Am Mittwoch, 13. März 2013 13:20:43 UTC+1 schrieb Cesar Inacio Martins: > Hi Fernando, > > > This is something what I try before , the problem is have all fields of same row togheter... where difficult use the data without include a treatment to separate each information. > > > > > > > expression  MULTISET{'1,1,foo       ','2,1,bar       ','3,1,baz       '} > > expression  MULTISET{'4,2,some      ','5,2,random    ','7,2,data      '} > > > > > expression  MULTISET{'6,3,Data 1    ','8,3,Data 2    ','9,3,Data 3    '} > > > > > > > 2013/3/13 Fernando Nunes <domus...@gmail.com> > > > > Food for thought: > > drop table teste; > create temp table teste ( id smallint, cat smallint, data char(10));> > > insert into teste values ( 1,      1,      'foo    ' ); > insert into teste values ( 2,      1,      'bar    ' );> > insert into teste values ( 3,      1,      'baz    ' ); > insert into teste values ( 4,      2,      'some   ' ); > insert into teste values ( 5,      2,      'random ' ); > insert into teste values ( 6,      3,      'Data 1 ' );> > > > insert into teste values ( 7,      2,      'data   ' ); > insert into teste values ( 8,      3,      'Data 2 ' ); > insert into teste values ( 9,      3,      'Data 3 ' ); > insert into teste values ( 10,     3,      'Data 4 ' );> > > > > select * from teste;> select ms.* > from > ( > SELECT MULTISET( SELECT ITEM t.id || ',' || t.cat || ',' || t.data m1 FROM teste t WHERE t.cat = tout.cat)  FROM (SELECT unique cat from teste) tout > > > > ) ms > > > > > > > On Wed, Mar 13, 2013 at 12:01 AM, Cesar Inacio Martins <cesar.inac...@gmail.com> wrote: > > > > > > > This is something where I always see as huge challenge when need to play only with DML on Informix. > > > > I play now a little with this... and get no easy/simple solution, so far... > > > > > Get this data : > > > > > drop table teste;> > > > > > create temp table teste ( id smallint, cat smallint, data char(10)); > insert into teste values ( 1,      1,      'foo    ' );> > > > > > insert into teste values ( 2,      1,      'bar    ' ); > insert into teste values ( 3,      1,      'baz    ' );> > > > > > insert into teste values ( 4,      2,      'some   ' ); > insert into teste values ( 5,      2,      'random ' );> > > > > > insert into teste values ( 6,      3,      'Data 1 ' ); > insert into teste values ( 7,      2,      'data   ' );> > > > > > insert into teste values ( 8,      3,      'Data 2 ' ); > insert into teste values ( 9,      3,      'Data 3 ' );> > > > > > > > and transform into this : > > > > > > cat     id1     data1   id2     data2   id3     data3 > ----------------------------------------------------- > 1       1       foo     2       bar     3       baz > > > > > > 2       4       some    5       random  7       data > 3       6       Data 1  8       Data 2  9       Data 3 > > > > > > > > Where the logic is : aggregate into single line the 3 lines what have the same "cat" . > This is possible on ifx 11.70 ?  or 12.1? > > > > > > Only with DML... no SPL. (consider the user/system don't have grant to create procedure) > > > > > Original question : http://stackoverflow.com/questions/15368750/aggregating-several-columns-to-single-colum > > > > > > > > Regards > Cesar > > > _______________________________________________ > > Informix-list mailing list > > Inform...@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > > > My email works... but I don't check it frequently...