Create a CSV Field (String Aggregation) with Recursive SQL for DB2

I was recently working on a client project where I had need to create a delimited field from multiple SQL rows returned for a query. To accomplish this I used a recursive SQL query that allows me to aggregate multiple results together into a single string with a delimiter.

NOTE: This query is designed to be run on DB2 and may or may not need to be adjusted to different database platforms.

The following list highlights the pieces of this query, and their purpose in the overall functionality:

• TMP1: The query to be run to retrieve your initial result set.
• TMP2: Is recursive (references itself), and performs the actual concatenation for each row returned within the intial result set.
• TMP3: The query used to actually retrieve the fully concatenated string field.

To utilize this query for a single field aggregate, replace the elements in the TMP1 query block and customize it to meet your specific needs.

WITH
	TMP1 (FIELD1, ROW_NUM) AS (
		SELECT {FIELD1}, ROW_NUMBER() OVER() AS ROW_NUM FROM {TABLE} ORDER BY {FIELD1}
	),
	TMP2 (CSV_STRING, CNT, CNT_MAX) AS (
		SELECT VARCHAR('', 1500), 0, COUNT(FIELD1) FROM TMP1
		UNION ALL
		SELECT
			CASE
				WHEN TMP2.CSV_STRING = '' THEN
					RTRIM(CHAR(TMP1.FIELD1))
				ELSE
					TMP2.CSV_STRING || ',' || RTRIM(CHAR(TMP1.FIELD1))
			END,
			TMP2.CNT + 1,
			TMP2.CNT_MAX
			FROM TMP1, TMP2
			WHERE TMP1.ROW_NUM = TMP2.CNT + 1
				AND TMP2.CNT < TMP2.CNT_MAX
	),
	TMP3 AS (
		SELECT CSV_STRING FROM TMP2 WHERE TMP2.CNT = TMP2.CNT_MAX
	)
SELECT * FROM TMP3 FETCH FIRST ROW ONLY;