Re: Challenging : Aggregating several columns to single line

select a.cat, a.id as id1, a.data as data1, b.id as id2, b.data as data2,c.id as id3, c.data as data3 from teste as a, teste as b, teste as c where a.cat = b.cat and b.cat = c.cat and a.id = 1 and b.id = 3 and c.id = 3; Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 12, 2013 at 8:01 PM, 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 > >