Convert Rows to One Column

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.

Incoming search terms:

• convert rows to columns in oracle
• convert row to column in oracle
• oracle rows to columns
• oracle rows to one column
• oracle select rows to column
• oracle xmlagg
• oracle xmlagg row to col
• report builder convert rows to columns
• row to col oracle
• row to column in oracle

No related posts.