Challenging : Aggregating several columns to single line

Discussion:

(too old to reply)

Cesar Inacio Martins

2013-03-13 00:01:39 UTC

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

Art Kagel

2013-03-13 00:49:08 UTC

Permalink

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 <

Post by Cesar Inacio Martins
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...
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 ' );
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)
http://stackoverflow.com/questions/15368750/aggregating-several-columns-to-single-colum
Regards
Cesar
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

Cesar Inacio Martins

2013-03-13 01:00:52 UTC

Permalink

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... :)

Post by Art Kagel
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;
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 <

Fernando Nunes

2013-03-13 03:32:27 UTC

Permalink

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 <

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Cesar Inacio Martins

2013-03-13 12:20:43 UTC

Permalink

Post by Cesar Inacio Martins
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 <

--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

s***@t-online.de

2013-03-14 20:56:54 UTC

Permalink

select a.*, b.* ,c.*
from teste a, teste b , teste c
where a.cat = b.cat and a.cat = c.cat and b.cat = c.cat
and a.rowid != b.rowid and a.rowid != c.rowid and b.rowid != c.rowid
and a.id < b.id and b.id < c.id
order by a.cat;

gives:

id cat data id cat data id cat data

1 1 foo 2 1 bar 3 1 baz
4 2 some 5 2 random 7 2 data
6 3 Data 1 8 3 Data 2 9 3 Data 3

assume its what is required and yes rowids YUK... would be nice to
have a PK on the table or a recordid...this will barf on a fragmented table...

Superboer.

Hi Fernando,
This is something what I try before , the problem is have all fields of same row togheter... where difficult use the data without include a treatment to separate each information.
expression MULTISET{'1,1,foo ','2,1,bar ','3,1,baz '}
expression MULTISET{'4,2,some ','5,2,random ','7,2,data '}
expression MULTISET{'6,3,Data 1 ','8,3,Data 2 ','9,3,Data 3 '}
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
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...
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 ' );
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
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...