SQL Question: Aggregate several rows in one line?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard Spitz - 14 Feb 2008 15:17 GMT

Dear Informixers,

I know this question has been discussed before, but I cannot think
of the right keywords to search for what I want to accomplish. Maybe
even the subject line is misleading, but I couldn't come up with
with anything better, I'm not a native English speaker.

The requirement to display a query over a master-detail-relationship
in one line, where the master-retail-relationship is one-to-many and
the query does an outher join between the master and the detail table.

So instead of the standard display:

master1    detail1
master1    detail2
master2    detail1
master3    detail3
master4
master5    detail2
master5    detail3
master5    detail4
...

it is to be displayed like this:

master1    detail1,detail2
master2    detail1
master3    detail3
master4
master5    detail2,detail3,detail4

Of course I know how to do this with a program in ESQL/C or 4GL, but
is there any way to do this with plain SQL? I've just migrated from
IDS 7.31 to IDS 10.00.UC6, so maybe there are new features in this
engine and the accompanying SQL that I am not yet familiar with.

I'd be very happy if you could point me to a solution, to save me
from having to write a couple of programs when just some fancy SQL
can give me the desired result.

Regards, Richard

Reply to this Message

Marco Greco - 14 Feb 2008 15:31 GMT

> Dear Informixers,
>
[quoted text clipped - 37 lines]
>
> Regards, Richard

If you go to my site and get sqsl (available in several forms, including a
dbaccess like app), you can then append a FORMAT specifier to your selects.
this will allow you to display rows horizontally, vertically, obliquely,
zigzag, mix multiple selects onto the same row, etc with full 4gl and / or c
format specifiers, and with no coding at all.

Signature

Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!

Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Reply to this Message

Art S. Kagel (Oninit LLC) - 14 Feb 2008 16:52 GMT

> Dear Informixers,
>
[quoted text clipped - 36 lines]
> can give me the desired result.
>

Can't think of any way except using some programming interface ESQL/C,
4GL, Java, SPL, etc. Perhaps it might be possible to a UDR in C or Java
to perform the result set folding, haven't tried.

Art S. Kagel
Oninit

> Regards, Richard

===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.oninit.com/home/disclaimer.php

===========================================================================================

Reply to this Message

SaltTan - 21 Feb 2008 06:09 GMT

On 14 фев, 18:17, Richard Spitz <Richard.Sp...@med.uni-muenchen.de>
wrote:

> Dear Informixers,
>
[quoted text clipped - 37 lines]
>
> Regards, Richard

MULTISET - the new feature

Select tabname,
replace(replace(replace(
multiset (select trim(grantee) from systabauth a
where a.tabid=t.tabid)::LVARCHAR
,'''),ROW(''',', '), 'MULTISET{ROW('''), ''')}')
From systables t
where t.tabid > 100
order by 1

Reply to this Message

Richard Spitz - 21 Feb 2008 11:22 GMT

SaltTan <SaltTan@gmail.com> schrieb:

>MULTISET - the new feature
>
[quoted text clipped - 6 lines]
>where t.tabid > 100
>order by 1

Wow, great! I had hoped that the new SQL features would make something
like this possible.

Now I just have to understand what you are doing here. The code looks
a little obfuscated ;-) But it is a starting point. Thanks very much!

Regards, Richard

Reply to this Message