Hi all,

ist there a comfortable way to turn a subquery that contains multiple
values into a list?

Example:

Table A:
ID - Name
-------------
1 - Bart
2 - Lisa
3 - Homer
4 - Marge


Table B:
User - Login
--------------
1 - 12:34
2 - 13:41
3 - 14:00
1 - 15:12
4 - 09:14
2 - 17:12

Query: something like
SELECT Name, (Select List(Login) from B where User=ID)
FROM A

Result:
Bart - 12:34, 15:12
Lisa - 13:41, 17:12
Homer - 14:00
Marge - 09:14

Is there something way I know would be to define a function that
receives the ID as parameter and returns the Logins as string, but I
wonderes whether there is a generic function or one of you knows how
to create one.

THX, Langi

Matthias Langbein wrote:
> Hi all,
>
> ist there a comfortable way to turn a subquery that contains multiple
> values into a list?
>
> Example:
>
> Table A:
> ID - Name
> -------------
> 1 - Bart
> 2 - Lisa
> 3 - Homer
> 4 - Marge
>
>
> Table B:
> User - Login
> --------------
> 1 - 12:34
> 2 - 13:41
> 3 - 14:00
> 1 - 15:12
> 4 - 09:14
> 2 - 17:12
>
> Query: something like
> SELECT Name, (Select List(Login) from B where User=ID)
> FROM A
>
> Result:
> Bart - 12:34, 15:12
> Lisa - 13:41, 17:12
> Homer - 14:00
> Marge - 09:14
>
> Is there something way I know would be to define a function that
> receives the ID as parameter and returns the Logins as string, but I
> wonderes whether there is a generic function or one of you knows how
> to create one.
>
> THX, Langi

www.psoug.org
click on Morgan's Library
click on Conditions
scroll down to "Complex IN Demo"
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org