Home » SQL & PL/SQL » SQL & PL/SQL » SQL Queries
SQL Queries [message #185536] Wed, 02 August 2006 03:37 Go to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Hi All,

Please extract record from EMP table suppose records are stored in given below

> Eno Ename Dept
10 Arvind SW
10 Arvind Prog
10 Ajay SW
11 Arvind SW

> Result must be

Eno Ename Dept
10 Arvind SW,Prog
10 Ajay SW
11 Arvind SW

I have tried below mention queryied but error occured in SYS_CONNECT_BY_PATH,please help me

SELECT ENO,ENAME ,SUBSTR(MAX(SYS_CONNECT_BY_PATH(DEPT,'/')),2) FROM
(
SELECT ENO,ENAME, DEPT,row_number() OVER(PARTITION BY ENO,ENAME ORDER BY ROWNUM) rn
FROM emp
)
START WITH rn=1
CONNECT BY PRIOR rn=rn-1
GROUP BY ENO,ENAME



Re: SQL Queries [message #185600 is a reply to message #185536] Wed, 02 August 2006 09:13 Go to previous message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Try this...

select eno,ename,substr(max(sys_connect_by_path(Dept,',')),2)dept
  from(select eno,ename,dept,row_number() over(partition by eno,ename order by eno,ename) r 
         from n_emp
	order by eno,ename)
CONNECT BY prior eno =eno 
       and prior ename = ename
       and prior r=r-1
START WITH r=1
group by eno,ename
order by eno,ename desc



Naveen
Previous Topic: how to get result into spool file
Next Topic: Question on Functions
Goto Forum:
  


Current Time: Tue Dec 06 14:22:19 CST 2016

Total time taken to generate the page: 0.08626 seconds