Home » SQL & PL/SQL » SQL & PL/SQL » Convert rows to cols with distinct, sys_connect_by_path (10g)
Convert rows to cols with distinct, sys_connect_by_path [message #430506] Tue, 10 November 2009 14:06 Go to next message
KTZ
Messages: 15
Registered: January 2005
Junior Member
I have a query that returns several rows into a column using the sys_connect_by_path and rownumber.
SELECT projectseq, LTRIM(MAX(SYS_CONNECT_BY_PATH (contractorname, '|')),'|') contr_String
FROM
    (SELECT  projectseq , contractorname, row_number() OVER ( PARTITION BY projectseq  ORDER BY rownum) as  rn
                from Contractor c, contract_xref x
                where C.CONTRACTORSEQ = X.CONTRACTORSEQ)
CONNECT  BY  projectseq = PRIOR projectseq
AND  rn     = PRIOR rn+1
START WITH rn =1           

GROUP BY projectseq
ORDER BY projectseq;


This gives a list something like this:
125 Miller Exc|Smith Const|Smith Const
130 Green Cont
142 Smith Const|Smith Const|Ace Ind|Smith Const

What I really want just a list of distincts. Like this:
125 Miller Exc|Smith Const
130 Green Cont
142 Smith Const|Ace Ind

Is this possible?

Thanks,
Kim
Re: Convert rows to cols with distinct, sys_connect_by_path [message #430562 is a reply to message #430506] Wed, 11 November 2009 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The inner query should be split in two levels:
(SELECT  projectseq , contractorname, 
         row_number() OVER ( PARTITION BY projectseq  ORDER BY rownum) as  rn
from 
   ( select distinct projectseq , contractorname
     from Contractor c, contract_xref x
     where C.CONTRACTORSEQ = X.CONTRACTORSEQ )
)

Regards
Michel

[Updated on: Wed, 11 November 2009 01:18]

Report message to a moderator

Re: Convert rows to cols with distinct, sys_connect_by_path [message #430837 is a reply to message #430506] Thu, 12 November 2009 08:20 Go to previous message
KTZ
Messages: 15
Registered: January 2005
Junior Member
Perfect, thanks!
Previous Topic: Increment sequence value for set of rows
Next Topic: Regarding High Water Mark and Index
Goto Forum:
  


Current Time: Sun Dec 04 02:55:31 CST 2016

Total time taken to generate the page: 0.12190 seconds