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

1651695

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

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

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!

RiteshSinha

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.

RiteshSinha

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

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!

RiteshSinha

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

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!

RiteshSinha

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

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.