Re: Challenging : Aggregating several columns to s
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-singl
e-colum
>>
>> Regards
>> Cesar
>>
>> _______________________________________________
>> Informix-list mailing list
>> Informix-list@iiug.org
>> http://www.iiug.org/mailman/listinfo/informix-list
>>
>>
>
--e89a8f3ba6e3480f8604d7c3f141
Can't you just use the node data type ?
Cheer
Paul
Paul Watson
Oninit www.oninit.com
+1 913 387 7529
On Mar 12, 2013, at 20:01, "Cesar Inacio Martins" <cesar.inacio.martins@gmai=
l.com> wrote:
> Sorry Art... don't work... missing the others cat rows.=20
>=20
> Remember, this is a example, the production table probably will have a lot=
=20
> of rows... not only 9 , so write SQL for each data content isn't much smar=
t.=20
>=20
> Your sql return only :=20
>=20
> cat id1 data1 id2 data2 id3 data3=20
>=20
> 1 1 foo 3 baz 3 baz=20
>=20
> The challenging continue... :)=20
>=20
> 2013/3/12 Art Kagel <art.kagel@gmail.com>=20
>=20
>> select a.cat, a.id as id1, a.data as data1, b.id as id2, b.data as data2,==20
>> c.id as id3, c.data as data3=20
>> from teste as a, teste as b, teste as c=20
>> where a.cat =3D b.cat and b.cat =3D c.cat=20
>>=20
>> and a.id =3D 1 and b.id =3D 3 and c.id =3D 3;
>=20
> 2013/3/12 Art Kagel <art.kagel@gmail.com>=20
>=20
>> select a.cat, a.id as id1, a.data as data1, b.id as id2, b.data as data2,==20
>> c.id as id3, c.data as data3=20
>> from teste as a, teste as b, teste as c=20
>> where a.cat =3D b.cat and b.cat =3D c.cat=20
>> and a.id =3D 1 and b.id =3D 3 and c.id =3D 3;=20
>>=20
>> Art=20
>>=20
>> Art S. Kagel=20
>> Advanced DataTools (www.advancedatatools.com)=20
>> Blog: http://informix-myview.blogspot.com/=20
>>=20
>> Disclaimer: Please keep in mind that my own opinions are my own opinions=20=
>> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any=20=
>> other organization with which I am associated either explicitly,=20
>> implicitly, or by inference. Neither do those opinions reflect those of=20=
>> other individuals affiliated with any entity with which I am affiliated n=
or=20
>> those of the entities themselves.=20
>>=20
>>=20
>> On Tue, Mar 12, 2013 at 8:01 PM, Cesar Inacio Martins <=20
>> cesar.inacio.martins@gmail.com> wrote:=20
>>=20
>>>=20
>>> This is something where I always see as huge challenge when need to play=
=20
>>> only with DML on Informix.=20
>>> I play now a little with this... and get no easy/simple solution, so=20
>>> far...=20
>>>=20
>>> Get this data :=20
>>>=20
>>> drop table teste;=20
>>> create temp table teste ( id smallint, cat smallint, data char(10));=20
>>> insert into teste values ( 1, 1, 'foo ' );=20
>>> insert into teste values ( 2, 1, 'bar ' );=20
>>> insert into teste values ( 3, 1, 'baz ' );=20
>>> insert into teste values ( 4, 2, 'some ' );=20
>>> insert into teste values ( 5, 2, 'random ' );=20
>>> insert into teste values ( 6, 3, 'Data 1 ' );=20
>>> insert into teste values ( 7, 2, 'data ' );=20
>>> insert into teste values ( 8, 3, 'Data 2 ' );=20
>>> insert into teste values ( 9, 3, 'Data 3 ' );=20>>>=20
>>> and transform into this :=20
>>>=20
>>> cat id1 data1 id2 data2 id3 data3=20
>>> -----------------------------------------------------=20
>>> 1 1 foo 2 bar 3 baz=20
>>> 2 4 some 5 random 7 data=20
>>> 3 6 Data 1 8 Data 2 9 Data 3=20
>>>=20
>>> Where the logic is : aggregate into single line the 3 lines what have th=
e=20
>>> same "cat" .=20
>>> This is possible on ifx 11.70 ? or 12.1?=20
>>> Only with DML... no SPL. (consider the user/system don't have grant to=20=
>>> create procedure)=20
>>>=20
>>> Original question :
> http://stackoverflow.com/questions/15368750/aggregating-several-columns-to=
-single-colum=20
>>>=20
>>> Regards=20
>>> Cesar=20
>>>=20
>>> _______________________________________________=20
>>> Informix-list mailing list=20
>>> Informix-list@iiug.org=20
>>> http://www.iiug.org/mailman/listinfo/informix-list
>=20
> --e89a8f3ba6e3480f8604d7c3f141=20
>=20
>=20
> **************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
Hi Paul,
I never used it , can you show me a basic example to try apply in this
situation?
2013/3/12 Paul Watson <paul@oninit.com>
> Can't you just use the node data type ?
>
> Cheer
> Paul
>
> Paul Watson
> Oninit www.oninit.com
> +1 913 387 7529
>
> On Mar 12, 2013, at 20:01, "Cesar Inacio Martins"
> <cesar.inacio.martins@gmai=
> l.com> wrote:
>
> > Sorry Art... don't work... missing the others cat rows.=20
> >=20
> > Remember, this is a example, the production table probably will have a
> lot=
> =20
> > of rows... not only 9 , so write SQL for each data content isn't much
> smar=
> t.=20
> >=20
> > Your sql return only :=20
> >=20
> > cat id1 data1 id2 data2 id3 data3=20
> >=20
> > 1 1 foo 3 baz 3 baz=20
> >=20
> > The challenging continue... :)=20
> >=20
> > 2013/3/12 Art Kagel <art.kagel@gmail.com>=20
> >=20
> >> select a.cat, a.id as id1, a.data as data1, b.id as id2, b.data as
> data2,=> =20
> >> c.id as id3, c.data as data3=20
> >> from teste as a, teste as b, teste as c=20
> >> where a.cat =3D b.cat and b.cat =3D c.cat=20
> >>=20
> >> and a.id =3D 1 and b.id =3D 3 and c.id =3D 3;
> >=20
> > 2013/3/12 Art Kagel <art.kagel@gmail.com>=20
> >=20
> >> select a.cat, a.id as id1, a.data as data1, b.id as id2, b.data as
> data2,=> =20
> >> c.id as id3, c.data as data3=20
> >> from teste as a, teste as b, teste as c=20
> >> where a.cat =3D b.cat and b.cat =3D c.cat=20
> >> and a.id =3D 1 and b.id =3D 3 and c.id =3D 3;=20
> >>=20
> >> Art=20
> >>=20
> >> Art S. Kagel=20
> >> Advanced DataTools (www.advancedatatools.com)=20
> >> Blog: http://informix-myview.blogspot.com/=20
> >>=20
> >> Disclaimer: Please keep in mind that my own opinions are my own
> opinions=20=
>
> >> and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> any=20=
>
> >> other organization with which I am associated either explicitly,=20
> >> implicitly, or by inference. Neither do those opinions reflect those
> of=20=
>
> >> other individuals affiliated with any entity with which I am affiliated
> n=
> or=20
> >> those of the entities themselves.=20
> >>=20
> >>=20
> >> On Tue, Mar 12, 2013 at 8:01 PM, Cesar Inacio Martins <=20
> >> cesar.inacio.martins@gmail.com> wrote:=20
> >>=20
> >>>=20
> >>> This is something where I always see as huge challenge when need to
> play=
> =20
> >>> only with DML on Informix.=20
> >>> I play now a little with this... and get no easy/simple solution, so=20
> >>> far...=20
> >>>=20
> >>> Get this data :=20
> >>>=20
> >>> drop table teste;=20
> >>> create temp table teste ( id smallint, cat smallint, data char(10));=20
> >>> insert into teste values ( 1, 1, 'foo ' );=20
> >>> insert into teste values ( 2, 1, 'bar ' );=20
> >>> insert into teste values ( 3, 1, 'baz ' );=20
> >>> insert into teste values ( 4, 2, 'some ' );=20
> >>> insert into teste values ( 5, 2, 'random ' );=20
> >>> insert into teste values ( 6, 3, 'Data 1 ' );=20
> >>> insert into teste values ( 7, 2, 'data ' );=20
> >>> insert into teste values ( 8, 3, 'Data 2 ' );=20
> >>> insert into teste values ( 9, 3, 'Data 3 ' );=20> >>>=20
> >>> and transform into this :=20
> >>>=20
> >>> cat id1 data1 id2 data2 id3 data3=20
> >>> -----------------------------------------------------=20
> >>> 1 1 foo 2 bar 3 baz=20
> >>> 2 4 some 5 random 7 data=20
> >>> 3 6 Data 1 8 Data 2 9 Data 3=20
> >>>=20
> >>> Where the logic is : aggregate into single line the 3 lines what have
> th=
> e=20
> >>> same "cat" .=20
> >>> This is possible on ifx 11.70 ? or 12.1?=20
> >>> Only with DML... no SPL. (consider the user/system don't have grant
> to=20=
>
> >>> create procedure)=20
> >>>=20
> >>> Original question :
> >
> http://stackoverflow.com/questions/15368750/aggregating-several-columns-to=
> -single-colum=20
> >>>=20
> >>> Regards=20
> >>> Cesar=20
> >>>=20
> >>> _______________________________________________=20
> >>> Informix-list mailing list=20
> >>> Informix-list@iiug.org=20
> >>> http://www.iiug.org/mailman/listinfo/informix-list
> >=20
> > --e89a8f3ba6e3480f8604d7c3f141=20
> >=20
> >=20
> >
> **************************************************************************=
> *****=20
> > Forum Note: Use "Reply" to post a response in the discussion forum.=20
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7bfcf9b6d2ce7404d7c4230c