HELP - Passing second value into separate column

[Darrel77]

Hello all...

I'm have a slight dilemma in SQL. I'm running the below query...

select DISTINCT a.acct,
p.first||' '||p.last OWNER,
p2.first||' '||p2.last OWNER2
from person p, person p2, act a, actperson arp
where a.NBR = p.nbr
and arp.nbr = p2.nbr
and a.acct = arp.acct

ACCT OWNER OWNER2
----- ----------- -----------------
12345 Martin Payne Gina Payne
11223 Vince McMahon Linda McMahon
11223 Vince McMahon Stephanie McMahon

I would like the above output to look like this...

ACCT OWNER OWNER2 OWNER3
----- ----------- ------------- -----------------
12345 Martin Payne Gina Payne
11223 Vince McMahon Linda McMahon Stephanie McMahon

There's really no other way to link to the account owners other than the way I did above. Is there any funky way via SQL for me to pass any second (or third, fourth, etc) into a new output column?

Any help would be greatly appreciated.

Thanks,
Darrel

 

[nigelrivett]

Something like

select ....
from
act a,
join actperson arp
on a.acct = arp.acct
join person p
on a.NBR = p.nbr
join person p2
on arp.nbr = p2.nbr
and p2.first||' '||p2.last = (select top 1 name from (select top 1 name = z.first||' '||z.last from person z where z.nbr = arp.nbr order by z.first||' '||z.last) a order by name)
left join person p3
on arp.nbr = p3.nbr
and p3.first||' '||p3.last = (select top 1 name from (select top 2 name = z.first||' '||z.last from person z where z.nbr = arp.nbr order by z.first||' '||z.last) a order by name)
left join person p4
on arp.nbr = p4.nbr
and p4.first||' '||p4.last = (select top 1 name from (select top 2 name = z.first||' '||z.last from person z where z.nbr = arp.nbr order by z.first||' '||z.last) a order by name)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.