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)

Here’s one.

XMLAGG (Available since Oracle 9i)


select rtrim (xmlagg (xmlelement (e, email_add || ',')).extract ('//text()'), ',') email_add
from email_groups
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.

Comments are closed.

Post Navigation