Re: Challenging : Aggregating several columns to single line
Posted in 2013
Topics: Stored Procedures & SPL, Security, Permissions & Auditing
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
>
>