Column aggregation in Oracle
Suppose we have a table (TBL) in Oracle with columns (CATEGORY, ATTR). Consider the following:
We want to convert the table as follows:
This can be done via the SQL below:
select CATEGORY,
xmlagg(xmlelement(c, ATTR || ',')).extract('//text()')
from TBL
group by CATEGORY
| CATEGORY | ATTR |
|---|---|
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | D |
| 2 | E |
| 2 | F |
| 3 | X |
| 3 | Y |
| 3 | Z |
We want to convert the table as follows:
| CATEGORY | ATTR |
|---|---|
| 1 | A,B,C |
| 2 | D,E,F |
| 3 | X,Y,Z |
This can be done via the SQL below:
select CATEGORY,
xmlagg(xmlelement(c, ATTR || ',')).extract('//text()')
from TBL
group by CATEGORY
Comments
Post a Comment