| LOV Query Display Problem [message #356583] |
Fri, 31 October 2008 05:27  |
sweetkhaliq
Messages: 200
Registered: April 2006
|
Senior Member |
|
|
Dear Group Members
I have the data as below
Table Name Emp_rec
empno empname join_date
11100 Astle 31/10/1978
11101 Adom 31/10/1978
when i run this query
select join_date,emp_no from emp_rec where join_date = '31-oct-1978';
then it shows data like this
join_date emp_no
31/10/1978 11100
31/10/1978 11101
But i require output as like this
join_date emp_no
31/10/1978 11100,11101
Tell me the query to show the require results.
Thanks and Regards
[Updated on: Fri, 31 October 2008 05:39] Report message to a moderator |
|
|
|
|
|
| Re: LOV Query Display Problem [message #356722 is a reply to message #356590] |
Sat, 01 November 2008 06:59  |
sweetkhaliq
Messages: 200
Registered: April 2006
|
Senior Member |
|
|
Thanks for reply
the function STRAGG did not work. It may be due to database version but i have used differt technique to solve this problem.
SELECT trunc(x.atten_date,'DD')atten_date, MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(x.emp_code),','),2))
KEEP(DENSE_RANK LAST ORDER BY x.curr)as_string
FROM (SELECT trunc(t.atten_date,'DD')atten_date,
t.emp_code,ROW_NUMBER()OVER (PARTITION BY trunc(t.atten_date,'DD') ORDER BY t.emp_code)curr,
ROW_NUMBER()OVER (PARTITION BY trunc(t.atten_date,'DD') ORDER BY t.emp_code) - 1 prev
FROM attendance t) x
where trunc(x.atten_date,'DD') = '01-nov-2008'
GROUP BY trunc(x.atten_date,'DD')
START WITH x.curr = 1
CONNECT BY x.prev = PRIOR x.curr
AND trunc(x.atten_date,'DD') = PRIOR trunc(x.atten_date,'DD');
Query Results
Atten_date Employees
01-nov-2008 002222,002223,002251,002225
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
