jeudi 3 janvier 2008

Aggregate varchar column with PL\SQL

I need to concatenate (Aggregate) all values (nvarchar) of grouped rows in an oracle table :
Table
Column1 Column2
V1 'Chaine1'
V1 'Chaine2'
V2 'Chaine3'
V2 'Chaine4'

The result expected :
V1 'Chaine1;Chaine2'
V2 'Chaine3;Chaine4'

2 Methods :
1. Create a User-Defined Aggregation function :
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
2. Use this Query :
SELECT Colonne1,
REPLACE(
REPLACE(xmlagg(xmlelement(unusedElem,Colonne2 ||
';'))
,'< UNUSEDELEMENT >')
,'< /UNUSEDELEMENT>')
FROM
TABLE
GROUP BY Colonne1