Tuesday, January 10, 2012

Column aggregation in Oracle

Suppose we have a table (TBL) in Oracle with columns (CATEGORY, ATTR). Consider the following:

CATEGORYATTR
1A
1B
1C
2D
2E
2F
3X
3Y
3Z

We want to convert the table as follows:

CATEGORYATTR
1A,B,C
2D,E,F
3X,Y,Z

This can be done via the SQL below:

select CATEGORY, 
       xmlagg(xmlelement(c, ATTR || ',')).extract('//text()') 
from TBL 
group by CATEGORY

No comments:

Post a Comment

CSP on Apache

To add CSP to root if sort of funny. The following will NOT work for most cases !!     <LocationMatch "^/$">        Header s...