Re: Challenging : Aggregating several columns to single line
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...