Oracle: Department wise Employee Ids Comma Separated (Analytical Functions )

select ids, deptno from
(
select LTRIM(ids,',') IDS, deptno, lvl, max(lvl) over (partition by deptno order by 1) mlvl from
(
    select sys_connect_by_path(empno,',') ids, deptno, level lvl from
    (
        select
            EMPNO,
            lead(EMPNO) over(partition by deptno order by 1) mgrno,
            deptno
        from scott.emp
    )
    connect by prior empno=mgrno
    start with mgrno is null
)
)where lvl=mlvl

Another Way To Get the Same

select distinct LTRIM(last_value(ids) over (partition by deptno order by 1),',') IDS, deptno from

(
    select sys_connect_by_path(empno,',') ids, deptno from
    (
       select
           EMPNO,
           lead(EMPNO) over(partition by deptno order by 1) mgrno,
           deptno
       from scott.emp
    )
    connect by prior empno=mgrno
    start with mgrno is null
    order by 1
)
order by 1 desc

Related Links:
- LISTAGG - ON OVERFLOW TRUNCATE - Oracle 12c R2
- Function to Compare two Comma Separated Strings in Oracle
- Oracle: New String Aggregation Techniques
- Oracle: Difference between wm_concat and ListAgg
- Oracle: Comma Separated String To Rows 11g
- Oracle: Comma Separated String To Rows 10g Onwards
- Oracle: Nth Record from Comma Separated String
- Oracle: Comma Separated String To Rows

4 comments:

Nimish GargFebruary 8, 2010 at 9:40 AM
My Frnd Rajat made it like:

SELECT
LTRIM(MAX(SYS_CONNECT_BY_PATH(empno,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees,
deptno
FROM
(
SELECT
deptno,
empno,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno) AS curr,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno) -1 AS prev
FROM SCOTT.EMP
)
GROUP BY deptno
CONNECT BY PREV = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1
AnonymousMarch 17, 2010 at 3:36 PM
CREATE FUNCTION [dbo].[udf_getStyle](@adpanelstyleID int)
RETURNS VARCHAR(500)
AS
BEGIN

DECLARE @style VARCHAR(2000)

SELECT @style=(coalesce(@style+' ; ','')+t.label+':'+apst.[value])
FROM adpanelstyles aps
INNER JOIN adpanelstyletags apst ON aps.adpanelstyleID=apst.adpanelstyleID AND apst.[value] IS NOT NULL
INNER JOIN tag t ON t.tagID=apst.tagID
WHERE aps.adpanelstyleID=@adpanelstyleID
Return @style

Table contains data-
Tag Value
------------------
Color #000000
font-weight normal
font-family arial
font-size 9pt

udf will return Output

Color:#000000 ; font-weight:normal ; font-family:arial ; font-size:9pt
Nirmesh TyagiFebruary 10, 2012 at 11:24 PM
Hi nimish, thanks 4 ur efforts. Can we make ths using WM-CONCAT, if nt thn why. . . . Why?
Nimish GargFebruary 11, 2012 at 9:02 AM
Nirmesh, yes you cab, but you should you LIST_AGG either. for detailed description check out
http://nimishgarg.blogspot.com/2010/07/oracle-differece-between-wmconcat-and.html

http://nimishgarg.blogspot.com/2010/02/oracle-new-string-aggregation.html