Pivot Database Rows Into A Single Column

There are times when a result single record is desired instead of multiple rows, for instance when joining a main table again multiple records in a child table. Say that I have an email and recipients table as follows

EMAILS
--------------------------------------------------
ID | SUBJECT      | EMAIL_TEXT   | SENDER
1  | Great Prices | See circular | AwesomeRetailer 

RECIPIENTS
------------------------
EMAIL_ID | RECEIPIENT
1        | bob@aol.com
1        | al@aol.com

Using a JOIN, you'd get back the following records.

ID | SUBJECT      | EMAIL_TEXT   | SENDER          | RECEIPIENT
1  | Great Prices | See circular | AwesomeRetailer | bob@aol.com
1  | Great Prices | See circular | AwesomeRetailer | al@aol.com

Which gives you all the information you need, but the id, subject, text, and from values are repeated, which means that as you're parsing through the results in Java, you have to 'look back' at the previous value to see if you're processing the same email, or a new one.

Alternatively, you can use the XMLAGG aggregate function to combine the records into a single row. This function will combine data together, then you just have to unpack it to make it into a string using the XMLSERIALIZE function. This approach requires a sub-select, because the XMLAGG function operates off a result set.

SELECT ID,
       SUBJECT,
       EMAIL_TEXT,
       SENDER,
       RTRIM((SELECT CAST(XMLSERIALIZE(XMLAGG(XMLTEXT(ADDR || ';')) AS CLOB)
         AS VARCHAR(500)) FROM RECIPIENTS_TABLE WHERE ID = EMAIL_ID)) AS RECIPIENTS

  FROM EMAILS

results in

ID | SUBJECT      | EMAIL_TEXT   | SENDER          | RECEIPIENTS
1  | Great Prices | See circular | AwesomeRetailer | bob@aol.com; al@aol.com