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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
References:
- http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions164.htm
- http://explainextended.com/2009/04/05/group_concat-in-oracle-10g/
- http://halisway.blogspot.ca/2006/08/oracle-groupconcat-updated-again.html
- http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
- http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions089.htm#SQLRF30030