Re: SQL Question: Aggregate several rows in one line?
On 14 фев, 18:17, Richard Spitz <Richard.Sp...@med.uni-muenchen.de>
wrote:
> 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
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
SaltTan <SaltTan@gmail.com> schrieb:
>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
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