Re: Challenging : Aggregating several columns to single line

Sorry Art... don't work... missing the others cat rows. Remember, this is a example, the production table probably will have a lot of rows... not only 9 , so write SQL for each data content isn't much smart. Your sql return only : cat id1 data1 id2 data2 id3 data3 1 1 foo 3 baz 3 baz The challenging continue... :) 2013/3/12 Art Kagel <art.kagel@gmail.com> > 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; 2013/3/12 Art Kagel <art.kagel@gmail.com> > 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 >> >> >