The Oracle (tm) Users' Co-Operative FAQ

How do I exclude duplicated elements from the “STRAGG” (String aggregation) in a SQL Statement?


Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

Date written: 15th November 2006

Oracle version(s): 10.2

How do I exclude duplicated elements from the “STRAGG” in a SQL Statement?

Back to index of questions


The following SQL Model Clause pattern can be used to exclude duplicates elements from the “STRAGG”. The requirements for this pattern are as following:

1)      Comma separated sorted id 

2)      No duplicate “item_str” are allowed within the same group.

3)      Current row’s “num_shift”  is between  previous row’s “num_shift”  - 1  and previous row’s “num_shift”  + 1

4)      Calculate both the sum/average “num_shift” for the “STRAGG” group.

 

create table t1 (item_name varchar2(3), num_shift number(5,2), id number(3), item_str varchar2(3) )

 

INSERT INTO t1 VALUES ('a1', 10,    1, 'A');

INSERT INTO t1 VALUES ('a1', 11,    2, 'B');

INSERT INTO t1 VALUES ('a1', 11.5,  3, 'C');

INSERT INTO t1 VALUES ('a1', 12,    4, 'A');

INSERT INTO t1 VALUES ('a1', 12.5,  5, 'B');

INSERT INTO t1 VALUES ('a1', 16,    6, 'C');

INSERT INTO t1 VALUES ('a1', 17 ,   7, 'B');

INSERT INTO t1 VALUES ('a1', 23 ,   8, 'B');

INSERT INTO t1 VALUES ('a1', 23 ,   9, 'C');

INSERT INTO t1 VALUES ('b1', 5,    10, 'W');

INSERT INTO t1 VALUES ('b1', 6,    11, 'Z');

INSERT INTO t1 VALUES ('c1', 3,    12, 'X');

INSERT INTO t1 VALUES ('c1', 4 ,   13, 'X');

INSERT INTO t1 VALUES ('c1', 5,    14, 'D');

INSERT INTO t1 VALUES ('c1', 6,    15, 'E');

INSERT INTO t1 VALUES ('c1', 7,    16, 'D');

 

SQL> select * from t1;

 

ITE  NUM_SHIFT         ID ITE                                                  

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

a1          10          1 A    (1,2,3 A/B/C  in the same group)                                              

a1          11          2 B                                                    

a1        11.5          3 C                                                    

a1          12          4 A    (4,5 A/B in the same group  )                                                     

a1        12.5          5 B                                                    

a1          16          6 C    (6,7 C/B in the same group )                                                     

a1          17          7 B                                                     

a1          23          8 B    (8,9 B/C in the same group)                                              

a1          23          9 C                                                    

b1           5         10 W    (10,11 W/Z in the same group)                                             

b1           6         11 Z                                                                                                      

c1           3         12 X    (12   X  )                                                 

c1           4         13 X    (13,14,15  X/D/E in the same group)                                              

c1           5         14 D                                                    

c1           6         15 E                                                    

c1           7         16 D    (16    D )

 

SQL> COLUMN item_name FORMAT A3

SQL> COLUMN id_Group FORMAT A12

SQL> COLUMN name_group FORMAT A8

 

SELECT item_name, id_Group, name_group, sum_num_shift,

       round(sum_num_shift/(LENGTH(id_Group) -

       LENGTH(REPLACE(id_Group, ',', '')) +1),2) avg_num_shift

  FROM

  (SELECT item_name, id_Group, sum_num_shift, name_group

     FROM

     (SELECT item_name, id_Group, sum_num_shift, name_group

        FROM  t1

        MODEL

        PARTITION BY  (item_name)

        DIMENSION BY

      (row_number() OVER (PARTITION BY item_name ORDER BY id, item_str)

         as position

      )

       MEASURES

      (

        num_shift,

        0 flag,

        CAST(NULL AS NUMBER) sum_num_shift,

        item_str item_str,

        CAST(id  AS VARCHAR2(3)) ID,

        CAST(NULL AS VARCHAR2(3255)) name_group,

        CAST(NULL AS VARCHAR2(3255)) IdGroup_temp,

        CAST(NULL AS VARCHAR2(3255)) id_Group

       )

       RULES  ITERATE(2)

      (

       id_Group[ ANY] ORDER BY position =

       CASE WHEN flag[CV()]  = 1

       THEN CASE WHEN IdGroup_temp[CV()+1] IS NOT NULL

                 THEN CASE WHEN instr(IdGroup_temp[CV()+1],

                                      IdGroup_temp[CV()]) < 1

                           THEN IdGroup_temp[CV()]

                      END

                 ELSE IdGroup_temp[CV()]

            END

       END,

      name_group[ ANY] ORDER BY  position   =

      CASE WHEN flag[CV()]  = 0

      THEN CASE WHEN abs( num_shift[CV()] -

                      nvl(num_shift[CV()-1], num_shift[CV()])) <= 1

                     AND instr(name_group[CV()-1], item_str[CV()]) <1

                THEN name_group[CV()-1]|| ',' ||item_str[CV()]

                ELSE item_str[CV()]

           END

      ELSE name_group[CV()]        

      END,

      IdGroup_temp[ANY] ORDER BY position =

      CASE WHEN flag[CV()] = 0

      THEN CASE WHEN abs( num_shift[CV()] -

                      nvl(num_shift[CV()-1], num_shift[CV()]))<= 1

                     AND  instr (name_group[CV()-1], item_str[CV()])<1

                THEN IdGroup_temp[CV()-1]|| ','|| ID [CV()]

                ELSE ID[CV()]

            END

      END , 

      sum_num_shift[ANY] ORDER BY  position =

      CASE WHEN flag[CV()]  = 0

      THEN CASE WHEN abs( num_shift[CV()] -

                       nvl(num_shift[CV()-1], num_shift[CV()])) <=1

                     AND instr(name_group[CV()-1], item_str[CV()])<1

                THEN  sum_num_shift[CV()-1]  + num_shift[CV()]

                ELSE  num_shift[CV()]

            END

      ELSE sum_num_shift[CV()]

      END ,

      flag[ ANY] = 1

     )

    )

  )

  WHERE  id_Group IS NOT NULL

  ORDER BY  item_name, id_Group ;

 

ITE ID_GROUP     NAME_GRO SUM_NUM_SHIFT AVG_NUM_SHIFT                          

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

a1  1,2,3        A,B,C             32.5         10.83                          

a1  4,5          A,B               24.5         12.25                          

a1  6,7          C,B                 33          16.5                          

a1  8,9          B,C                 46            23                          

b1  10,11        W,Z                 11           5.5                          

c1  12           X                    3             3                          

c1  13,14,15     X,D,E               15             5                          

c1  16           D                    7             7                          

 

8 rows selected.

 

SQL> spool off;

 


 

Back to top

Back to index of questions