Perform string aggregation?
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
Working with string concatenation
A few months back, in the OTN forum there was an interesting challenge:
http://forums.oracle.com/forums/thread.jspa?threadID=1047415&tstart=0
I've finally gotten around to addressing it after removing the original post's reserved words.
The blue highlights, below, are to draw your eyes to the non-trivial parts of the solutions.
CREATE TABLE t1 (
code      VARCHAR2(6),
code_type VARCHAR2(3),
AMOUNT NUMBER);

INSERT INTO t1 VALUES ('1001','10',1000);
INSERT INTO t1 VALUES ('1001','20',2000);
INSERT INTO t1 VALUES ('1001','30',3000);
INSERT INTO t1 VALUES ('1002','10',5000);
INSERT INTO t1 VALUES ('1002','20',3000);
INSERT INTO t1 VALUES ('1003','30',2500);

CREATE TABLE t2 (
code_type VARCHAR2(3),
code_name VARCHAR2(20));

INSERT INTO T2 VALUES ('10','type10');
INSERT INTO T2 VALUES ('20','type20');
INSERT INTO T2 VALUES ('30','type30');
INSERT INTO T2 VALUES ('40','type40');

SELECT * FROM t1;
SELECT * FROM t2;
BluShadow's solution
col equation format a50
col total format a20

SELECT code, equation, dbms_xmlgen.getxmltype('SELECT '|| math || ' FROM dual').EXTRACT
  ('ROWSET/ROW/*/text()') AS total
FROM (
  SELECT code,
         LTRIM(SYS_CONNECT_BY_PATH(code_type || ' ' || amount, '+'), '+') AS EQUATION,
         LTRIM(SYS_CONNECT_BY_PATH(amount, '+'), '+') AS MATH
 FROM (
   SELECT t1.code, t2.code_type, t1.amount, ROW_NUMBER()
          OVER (PARTITION BY t1.code ORDER BY t2.code_type) AS rn
   FROM t1 JOIN t2 ON (t2.code_type = t1.code_type))
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY code = PRIOR code
AND rn = PRIOR rn + 1
START WITH rn = 1)
ORDER BY 1;

EXPLAIN PLAN
-------------------------------------------------------------------------------
| Id | Operation                                  | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                           |    6 | 24054 |     21 (15)|
|  1 |  SORT ORDER BY                             |    6 | 24054 |     21 (15)|
|  2 |   VIEW                                     |    6 | 24054 |     21 (15)|
|* 3 |    FILTER                                  |      |       |            |
|* 4 |     CONNECT BY NO FILTERING WITH START-WITH|      |       |            |
|  5 |      VIEW                                  |    6 |   204 |     20 (10)|
|  6 |       WINDOW SORT                          |    6 |   144 |     20 (10)|
|* 7 |        HASH JOIN                           |    6 |   144 |     19  (6)|
|  8 |         TABLE ACCESS FULL T2               |    4 |    12 |      9  (0)|
|  9 |         TABLE ACCESS FULL T1               |    6 |   126 |      9  (0)|
-------------------------------------------------------------------------------
Michaels2's solution
col total format a50

SELECT code,
       extractvalue(xmltype(REPLACE(xmlagg(xmlelement(e, code_name || ' ' || amount)
       ORDER BY code_name), '</E><E>', ' + ')), 'E') || ' = ' || SUM(amount) AS TOTAL
FROM t1, t2
WHERE t1.code_type = t2.code_type
GROUP BY code;

EXPLAIN PLAN
------------------------------------------------------------
| Id | Operation               | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------
|  0 | SELECT STATEMENT        |    6 |   216 |     20 (10)|
|  1 |  SORT GROUP BY          |    6 |   216 |     20 (10)|
|* 2 |   HASH JOIN             |    6 |   216 |     19  (6)|
|  3 |    TABLE ACCESS FULL T2 |    4 |    60 |      9  (0)|
|  4 |    TABLE ACCESS FULL T1 |    6 |   126 |      9  (0)|
----------------------------------------------------------------------------
Morgan's solution with WM_CONCAT solution
SELECT t1.code, t2.code_name, t1.amount
FROM t1, t2
WHERE t1.code_type = t2.code_type;

SELECT code, TRANSLATE(WM_CONCAT(amount),',','+') AS TOTAL, SUM(amount) SUM
FROM (
  SELECT t1.code, t2.code_name, t1.amount
  FROM t1, t2
  WHERE t1.code_type = t2.code_type)
GROUP BY code;

EXPLAIN PLAN
------------------------------------------------------------
| Id | Operation               | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------
|  0 | SELECT STATEMENT        |    6 |   144 |     20 (10)|
|  1 |  SORT GROUP BY          |    6 |   144 |     20 (10)|
|* 2 |   HASH JOIN             |    6 |   144 |     19  (6)|
|  3 |    TABLE ACCESS FULL T2 |    4 |    12 |      9  (0)|
|  4 |    TABLE ACCESS FULL T1 |    6 |   126 |      9  (0)|
------------------------------------------------------------
LIST_AGG solution.
SELECT code, LISTAGG(amount, ' + ') WITHIN GROUP (ORDER BY code_name) AS TOTAL, SUM(amount) SUM
FROM (
  SELECT t1.code, t2.code_name, t1.amount
  FROM t1, t2
  WHERE t1.code_type = t2.code_type)
GROUP BY code;

EXPLAIN PLAN
------------------------------------------------------------
| Id | Operation               | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------
|  0 | SELECT STATEMENT        |    6 |   216 |     20 (10)|
|  1 |  SORT GROUP BY          |    6 |   216 |     20 (10)|
|* 2 |   HASH JOIN             |    6 |   216 |     19  (6)|
|  3 |    TABLE ACCESS FULL T2 |    4 |    60 |      9  (0)|
|  4 |    TABLE ACCESS FULL T1 |    6 |   126 |      9  (0)|
------------------------------------------------------------
I will let my readers look at the code, and the resulting explain plans, and decide for themselves what they might do in a similar situation.

What I do find fascinating is that, at least to the Oracle CBO optimizer, the final three look close enough to identical to reduce down to the same plan.
 
Related Topics
Oracle Functions
SQL*Plus
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved