Tried distinct however does not work to eliminate repeat output of
department name. Only want department name to print once with all
members then next department name and members etc.
select (cccb_name) as 'cccb', CONCAT(member_fname,' ',member_lname) as
'member ' from member, cccb, member_cccb_lnk where cccb.cccb_id =
member_cccb_lnk.cccb_id
Advisory department Tom Baster
Advisory department Jim Hooker
Advisory department Frank Mustard
Advisory department Julie Trees
Advisory department Jeff Black
Advisory department Louis Chad
Affirmative Action Department Jeff Black
Approval Department Julie Trees
Development department Jeff Black
Ethics department Jim Hooker
Finance department Tom Baster
Finance department Tony Tomas
Finance department Jim Hooker
Finance department Denny Rest
user wrote:
> Tried distinct however does not work to eliminate repeat output of
> department name. Only want department name to print once with all
> members then next department name and members etc.
> select (cccb_name) as 'cccb', CONCAT(member_fname,' ',member_lname) as
> 'member ' from member, cccb, member_cccb_lnk where cccb.cccb_id =
> member_cccb_lnk.cccb_id
> Advisory department Tom Baster
> Advisory department Jim Hooker
> Advisory department Frank Mustard
> Advisory department Julie Trees
> Advisory department Jeff Black
> Advisory department Louis Chad
> Affirmative Action Department Jeff Black
> Approval Department Julie Trees
> Development department Jeff Black
> Ethics department Jim Hooker
> Finance department Tom Baster
> Finance department Tony Tomas
> Finance department Jim Hooker
> Finance department Denny Rest
Try:
SELECT CASE WHEN ROW_NUMBER() OVER(PARTITION BY cccb_name) = 1 THEN
cccb_name END ....
Cheers
Serge
PS: Lost my Oracle privileges, so... untested
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
-----------------------------------------------Reply-----------------------------------------------
On May 9, 2:57 pm, user <some
@someplace.org> wrote:
> Tried distinct however does not work to eliminate repeat output of
> department name. Only want department name to print once with all
> members then next department name and members etc.
> select (cccb_name) as 'cccb', CONCAT(member_fname,' ',member_lname) as
> 'member ' from member, cccb, member_cccb_lnk where cccb.cccb_id =
> member_cccb_lnk.cccb_id
> Advisory department Tom Baster
> Advisory department Jim Hooker
> Advisory department Frank Mustard
> Advisory department Julie Trees
> Advisory department Jeff Black
> Advisory department Louis Chad
> Affirmative Action Department Jeff Black
> Approval Department Julie Trees
> Development department Jeff Black
> Ethics department Jim Hooker
> Finance department Tom Baster
> Finance department Tony Tomas
> Finance department Jim Hooker
> Finance department Denny Rest
>From SQL> prompt:
break on cccb skip 1
select (cccb_name) as 'cccb',
CONCAT(member_fname,' ',member_lname) as 'member'
from member, cccb, member_cccb_lnk
where cccb.cccb_id =member_cccb_lnk.cccb_id;
clear breaks
David Fitzjarrell
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
