I want to write a query so that it does functionality of listagg in oracle but I don't want to use it. Actually I don't want any function like lag and lead also. My table is-
Emp_id Emp_name Department 1 bradley 1 2 david 1 3 will 2 4 john 2 5 bale 2 6 alan 3 7 doug 3 8 hagar 4 9 carla 4 10 vashi 1
I want output like this-
department emp_name 1 bradley,david, vashi 2 will ,john ,bale 3 alan, doug 4 hagar, carla
I have made a query for this but here I don't want to use sys_connect_by_path. My query is-
SELECT department, LTRIM(max(SYS_CONNECT_BY_PATH(emp_name,',')) KEEP (DENSE_RANK LAST ORDER BY emp_name),',')AS employees FROM (SELECT department, emp_name, ROW_NUMBER() OVER (PARTITION BY department ORDER BY emp_name) AS emp, ROW_NUMBER() OVER (PARTITION BY department ORDER BY emp_name) -1 AS prev FROM employee4) GROUP BY department CONNECT BY prev = PRIOR emp AND department = PRIOR department START WITH emp = 1;
Can anyone help me?