The Stupid Box

This post is about string aggregation in oracle. Let’s say you have a table structure like below

And if you want to get an output like this

There are lot of ways you can achieve this. Like,
collect

select country, CAST(COLLECT(states) AS number_ntt) AS States from table;

(supported from 10g)

in the above query, number_ntt is a table with below definition
CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);

And you have to write your own function to convert the table structure to a string. like this

stragg

select country, stragg(states) from table;

this query is supported from only

listagg

SELECT country, LISTAGG(states, ',') WITHIN GROUP (ORDER BY states) AS states
FROM   table
GROUP BY country;

this function also supported from 11g version only. But you have additional facility like, you can sort them.

And there are few more techniques you can find them here

XML

I read about one more nice method.. couldn’t remember the link :() which I think is the coolest of all…

select xmlelement("country", xmlattributes(country as "country"), xmlagg(xmlelement("state",state))) from table
group BY country;

Once you get your data in XML format, you can format it to your desired output. XSL is for that… you can either format in oracle itself using XMLTRANSFORM function or using any XSL provider.

after writing above all, found a very good article of the same… I can’t blog anything if I have to write on my own.

Happy querying!!