Forum Stats

  • 3,851,914 Users
  • 2,264,053 Discussions
  • 7,904,902 Comments

Discussions

How do i display multiple rows as One Row by Eliminating Duplicates among them

1651695
1651695 Member Posts: 4
edited May 16, 2014 4:17PM in SQL & PL/SQL

How do I display multiple rows data as one Row? And also eliminate any duplicates among them..

For Example

Column1              Column2

A0                       JAN, FEB, MAR, SEP

B0                       JAN, FEB

C0                       JUN, JUL, AUG, SEP, OCT, NOV, DEC

My Output should be

COLUMN2

JAN,FEB,MAR,SEP,JUN,JUL,AUG,OCT,NOV,DEC

RiteshSinha

Answers

  • RiteshSinha
    RiteshSinha Member Posts: 9
    edited May 16, 2014 2:02PM

    I have used listagg to achieve this. Created a sample table and inserted the data as mentioned by you. Please see the below example.

    CREATE TABLE TEST (COLUMN1 VARCHAR2(10), COLUMN2 VARCHAR2(10));

    INSERT INTO TEST VALUES ('A0', 'JAN');

    INSERT INTO TEST VALUES ('A0', 'FEB');

    INSERT INTO TEST VALUES ('A0', 'MAR');

    INSERT INTO TEST VALUES ('A0', 'SEP');

    INSERT INTO TEST VALUES ('B0', 'JAN');

    INSERT INTO TEST VALUES ('B0', 'FEB');

    INSERT INTO TEST VALUES ('C0', 'JUN');

    INSERT INTO TEST VALUES ('C0', 'JUL');

    INSERT INTO TEST VALUES ('C0', 'AUG');

    INSERT INTO TEST VALUES ('C0', 'SEP');

    INSERT INTO TEST VALUES ('C0', 'OCT');

    INSERT INTO TEST VALUES ('C0', 'DEC');

    SELECT COLUMN1, LISTAGG(COLUMN2, ', ') WITHIN GROUP (ORDER BY COLUMN2)

      FROM TEST

    GROUP BY COLUMN1;

    Column1              Column2

    A0                       JAN, FEB, MAR, SEP

    B0                       JAN, FEB

    C0                       JUN, JUL, AUG, SEP, OCT, NOV, DEC

    SELECT LISTAGG(COLUMN2, ', ') WITHIN GROUP (ORDER BY COLUMN2) COLUMN2

      FROM (SELECT DISTINCT COLUMN2 FROM TEST);

    LISTAGG(COLUMN2,',')WITHINGROUP(ORDERBYCOLUMN2)                                                                                                                                                                                                                                                                                                                   --------------------------------------------------

    AUG, DEC, FEB, JAN, JUL, JUN, MAR, OCT, SEP                                                                                                                                                                                                                                                                                                                                                                                                                                                         

    Change the order by clause to get the data in the asked order:                                                

    SELECT LISTAGG(COLUMN2, ', ') WITHIN GROUP (ORDER BY to_date(COLUMN2,'MM')) COLUMN2

      FROM (SELECT DISTINCT COLUMN2 FROM TEST);


    COLUMN2

    -------------------------

    JAN, FEB, MAR, JUN, JUL, AUG, SEP, OCT, DEC                                                                                                                                                                                                                                                                                                                                                                                                   

  • 1651695
    1651695 Member Posts: 4

    Thanks Ritesh!

    I am using Database version 11.1.0.7 , looks like "LISTAGG"  function is not supported here.. I am getting an error stating  "FROM keyword not found where expected" . Its pointing to "WITHIN GROUP" clause..

    My Query is as follows:

    SELECT LISTAGG(ATTRIBUTE1, ', ') WITHIN GROUP (ORDER BY ATTRIBUTE1) ATTRIBUTE1

      FROM (SELECT DISTINCT ATTRIBUTE1 FROM apps.fnd_flex_values_vl

                                     where flex_value_Set_id = 1005220

                                     and flex_value in ('A0','D0','Q1'));

    Any idea…Is this the version issue? If so, do you know any alternate method?

    Regards!

  • This is the version issue.

    I would be using PL/SQL here then to help us with aggregation. Something like this below.

    SET SERVEROUTPUT ON SIZE 100000

    DECLARE

      CURSOR C1 IS

      SELECT DISTINCT COLUMN2

        FROM TEST;

      V_STRING VARCHAR2(500);

    BEGIN

      FOR I IN C1

      LOOP

        V_STRING := V_STRING ||','|| I.COLUMN2;

      END LOOP;

      DBMS_OUTPUT.PUT_LINE(V_STRING);

    END;

    anonymous block completed

    ,JAN,JUL,FEB,OCT,AUG,MAR,SEP,JUN,DEC

    We can create a function and call it from a query.

  • If you are comfortable with sys_connect_py_path, then this also will work,

    SELECT MAX(sys_connect_by_path(column2,','))

    FROM   (SELECT column2,

                   row_number() OVER (ORDER BY to_date(column2,'MM')) AS current_data,

                   row_number() OVER (ORDER BY to_date(column2,'MM')) -1 AS previous_data

            FROM   (SELECT DISTINCT column2 FROM TEST) TEST)

    CONNECT BY previous_data = PRIOR current_data

    START WITH current_data = 1;

    -------------------------------------------------------------------

    ,JAN,FEB,MAR,JUN,JUL,AUG,SEP,OCT,DEC

  • 1651695
    1651695 Member Posts: 4

    This pl/sql block concatenates all rows to display as one row, But does not eliminate duplicate values..

    My output is

    ,MAR, JUN, SEP,JAN, FEB, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV,DEC

    Please note JUN & SEP are repeating

    Thanks!

  • In cursor we have to fetch distinct data, if you see my block above i am fetching distinct values

    DECLARE

      CURSOR C1 IS

    SELECT DISTINCT COLUMN2

        FROM TEST;

    You use distinct and this will work for you.

  • 1651695
    1651695 Member Posts: 4

    Appreciate your efforts..

    Please note my data is not in this format

    C1     C2

    A0     JAN

    A0     FEB

    A0     MAR

    D0     JAN

    D0     DEC

    Q0     MAR

    Q0     SEP

    Q0     DEC

    Its in the format below

    C1     C2

    A0     JAN,FEB,MAR,

    D0     JAN,DEC

    Q0     MAR,SEP,DEC

    Do you think the pl/sql block, will still work?

    Thanks!

  • Ok. If this the case then our block requires modification, I have inserted sample data as you mentioned.

    Please see below the modified code.

    select * from test;

    COLUMN1    COLUMN2 

    ---------- ----------

    A0         JAN       

    A0         FEB       

    A0         MAR       

    A0         SEP       

    B0         JAN       

    B0         FEB       

    C0         JUN       

    C0         JUL       

    C0         AUG       

    C0         SEP       

    C0         OCT       

    C0         DEC       

    C0         DEC       

    C0         DEC, OCT  

    SET serveroutput ON SIZE 100000

    DECLARE

      CURSOR c1

      IS

        SELECT listagg(column2, ', ') WITHIN GROUP (

        ORDER BY column2) column2

        FROM TEST;

      l_index     NUMBER;

      l_element VARCHAR2(32767);

      l_text    VARCHAR2(32767);

      loop_str     NUMBER;

      build_str VARCHAR2(3000) := '';

      v_check   NUMBER         := 0;

    BEGIN

      FOR i IN c1

      loop

        l_text := i.column2;

        loop_str  := regexp_count(l_text, ',');

        FOR i IN 1 .. loop_str

        loop

          l_index       := instr(l_text, ',');

          l_element   := trim(substr(l_text, 1, l_index - 1));

          l_text      := substr(l_text, l_index         + 1);

          v_check     := nvl(regexp_count(build_str, l_element),0);

          IF v_check   = 0 THEN

            build_str := build_str ||','|| l_element;

          END IF;

        END loop;

        dbms_output.put_line(build_str);

      END loop;

    END;

    anonymous block completed

    ,AUG,DEC,OCT,FEB,JAN,JUL,JUN,MAR,SEP

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,726 Red Diamond
    edited May 16, 2014 4:18PM

    Assuming [email protected] is list of 3 letter month abbreviations:

    with mon as (

                 select  level n,

                         to_char(add_months(date '2013-12-01',level),'MON') m

                   from  dual

                   connect by level <= 12

                )

    select  rtrim(xmlagg(xmlelement(m,m,', ').extract('//text()') order by min(n)),', ') column2

      from  mon,

            tbl

      where column2 like '%' || m || '%'

      group by m

    /


    COLUMN2
    -------------------------------------------------
    JAN, FEB, MAR, JUN, JUL, AUG, SEP, OCT, NOV, DEC

    SQL>

    SY.

    RiteshSinha
This discussion has been closed.