Converting rows to column is a breeze if you are using Microsoft Access or reporting tools like Oracle Reports Builder. However, there are times that you’ll also need it for something else. I mean like displaying rows to one column in a SQL*Plus report. So how leh? (imagine that saying with a Singaporean accent)
XMLAGG (Available since Oracle 9i)
select rtrim (xmlagg (xmlelement (e, email_add || ',')).extract ('//text()'), ',') email_add
where email_group = '1-A945-9';
In that example, I needed to display all the email addresses in an email group. The output should be separated by comma so that I can pass it onto the email client without any problem nor any additional codes.
There’s more at Burleson Consulting in case you want other techniques.