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!!