markd_mms wrote:

>hi all
>
>not sure if nested query is the right term or not. basically what i'd
>like to do is this...
>
>i have a table called SOFTWARE which lists customer id's and software
>id's for all the software that our customers have installed. what i'd
>like to do is for each customer, select the software they have
>installed in different columns rather than customer by customer...
>
>so rather than do SELECT * FROM SOFTWARE WHERE "CustomerID" = blah
>and it list
>Windows XP
>Office XP
>...
>
>i'd like to have it like this
>Customer 1, Windows XP, Office XP
>Customer 2, Some other software
>
>is this possible?
>
>TIA
>
>

Mark,

I think this task will better acomplished on the client side.

You could make an selectable SP that concats the customers softwares,
but I don't think it will be an ellegant solution.

create procedure Customer_Software returns (CustomerID integer,
CustomerName varchar(40), Software varchar(32000)) as

declare variable wSoftware varchar(40);

begin
for
select
CustomerID, CustomerName
from
Customers
into
:CustomerID, :CustomerName
do begin
Software = '';

for
select
SoftwareName
from
Software
where
CustomerID = :CustomerID
into
wSoftware
do begin
if (Software = '') then
Software = wSoftware;
else
Software = Software + ', ' + wSoftware;
end;

Suspend;
end;
end


Maybe there is some ';' missing or in wrong places (I never know where I
need to put them) take this as a sample to guide you, but I would go to
do this on the client side.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br