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:

  1. 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

    ReplyDelete
  2. 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

    ReplyDelete
  3. Hi nimish, thanks 4 ur efforts. Can we make ths using WM-CONCAT, if nt thn why. . . . Why?

    ReplyDelete
  4. 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

    ReplyDelete