How do I implement a “STRAGG” (string aggregation) solution in a SQL Statement

2011-01-01

488

The Oracle (tm) Users' Co-Operative FAQ

How do I implement a STRAGG Solution in an SQL statement?

Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

Date written: 22^th November 2006

Oracle version(s): 10.2

How do I implement a STRAGG solution in a SQL Statement

Back to index of questions

The following SQL Model Clause Pattern can be used to implement a STRAGG Solution in a single SQL query.

SQL> select * from t1;

DE NAME

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

D1 Simon

D1 Jon

D1 Frank

D2 Peter

D2 Adam

D3 Tom

D3 Lee

D3 Jack

SELECT dept, name_Str

from

(SELECT dept, name_Str, rn, counter

FROM t1

MODEL

PARTITION BY (dept)

DIMENSION BY (row_number() over (PARTITION BY dept ORDER BY rowid) rn )

MEASURES (name, CAST(NULL AS VARCHAR2(3999)) name_Str,

count(*) OVER (PARTITION BY dept) counter

)

RULES (

name_Str[ANY] ORDER BY rn =

CASE WHEN name[cv() - 1 ] IS NULL

THEN name[cv()]

ELSE name_Str[cv()-1]||','|| name[cv()]

END

)

WHERE counter = rn

ORDER BY dept;

DE NAME_STR

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

D1 Simon,Jon,Frank

D2 Peter,Adam

D3 Tom,Lee,Jack

The following is a 9I Solution :

SELECT dept, MAX(LTRIM(sys_connect_by_path(name, ',' ) , ',')) name_str

FROM

(

SELECT dept, name,

row_number() over (PARTITION BY dept ORDER BY ROWID) rn

FROM t1

)

START WITH rn = 1

CONNECT BY dept = PRIOR dept AND PRIOR rn = rn -1

GROUP BY dept

ORDER BY DEPT;