Patlaf » Tag

MySQL has a very useful function called GROUP_CONCAT() that concatenate the aggregated values of a group (GROUP BY). With Oracle 11G, you can use listagg to do the same, but Oracle 10G does not have an equivalent function.

If you are still working with Oracle 10G, this is the simplest way I found to concatenate aggregated values.

WITH data AS (
SELECT CLASSIFICATION_PARENT
, CLASSIFICATION_VALUE
, ROW_NUMBER() OVER (PARTITION BY CLASSIFICATION_PARENT ORDER BY CLASSIFICATION_PARENT) AS rn
, COUNT(*) OVER (PARTITION BY CLASSIFICATION_PARENT) AS cnt
FROM DIM_CLASSIFICATION
WHERE CLASSIFICATION_PARENT = '210401'
)

SELECT CLASSIFICATION_PARENT, LTRIM(SYS_CONNECT_BY_PATH(CLASSIFICATION_VALUE,'|'),'|') AS CLASSIFICATION_VALUE
FROM data
WHERE rn=cnt
START WITH RN = 1
CONNECT BY PRIOR CLASSIFICATION_PARENT = CLASSIFICATION_PARENT AND PRIOR RN = RN-1
ORDER BY CLASSIFICATION_PARENT

WITH data AS (

SELECT CLASSIFICATION_PARENT

, CLASSIFICATION_VALUE

, ROW_NUMBER() OVER (PARTITION BY CLASSIFICATION_PARENT ORDER BY CLASSIFICATION_PARENT) AS rn

, COUNT(*) OVER (PARTITION BY CLASSIFICATION_PARENT) AS cnt

FROM DIM_CLASSIFICATION

WHERE CLASSIFICATION_PARENT = '210401'

)

SELECT CLASSIFICATION_PARENT, LTRIM(SYS_CONNECT_BY_PATH(CLASSIFICATION_VALUE,'|'),'|') AS CLASSIFICATION_VALUE

FROM data

WHERE rn=cnt

START WITH RN = 1

CONNECT BY PRIOR CLASSIFICATION_PARENT = CLASSIFICATION_PARENT AND PRIOR RN = RN-1

ORDER BY CLASSIFICATION_PARENT

The separator used CANNOT be present in any of the concatenated values . It can be a problem if you deal with large text. I used “|” because I know it’s a character that cannot be present in my values.

Original Data:

Concatenated Data

Oracle Group Concat 2

References:

Oracle equivalent of MySQL group_concat