Re: Challenging : Aggregating several columns to s

Posted in 2013

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

Fernando — Wed, 13 Mar 2013 03:32:32 — source: IIUG Forums & Mailing Lists

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-singl e-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... --20cf307c9bba2c9dd204d7c60e77

↪ replying to Fernando

Fernando — Wed, 13 Mar 2013 04:04:35 — source: IIUG Forums & Mailing Lists

An option... The weird stuff with min/max would not work for more than 3 items, but the use of rownum could solve it... basically I need to fetch different rows... min(id), max(id) and the "other". select cat, (select id from teste t1 where t1.cat = t.cat and id = (select min(id) from teste ta where ta.cat = t.cat)) id1, (select data from teste t1 where t1.cat = t.cat and id = (select min(id) from teste ta where ta.cat = t.cat)) data1, (select id from teste t1 where t1.cat = t.cat and id not in (select min(id) from teste ta where ta.cat = t.cat union select max(id) from teste ta where ta.cat = t.cat)) id2, (select data from teste t1 where t1.cat = t.cat and id not in (select min(id) from teste ta where ta.cat = t.cat union select max(id) from teste ta where ta.cat = t.cat)) data2, (select id from teste t1 where t1.cat = t.cat and id = (select max(id) from teste ta where ta.cat = t.cat)) id3, (select data from teste t1 where t1.cat = t.cat and id = (select max(id) from teste ta where ta.cat = t.cat)) data3 FROM teste t GROUP BY 1 cat id1 data1 id2 data2 id3 data3 2 4 some 5 random 7 data 3 6 Data 1 8 Data 2 9 Data 3 1 1 foo 2 bar 3 baz I suppose the query plan can harm your brain ;) On Wed, Mar 13, 2013 at 3:32 AM, Fernando Nunes <domusonline@gmail.com>wrote: > 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-singl e-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... > > --20cf307c9bba2c9dd204d7c60e77 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --bcaec54fb0fcc5e16c04d7c6805b