Concatenation of multiple row string values to one column in DB2

Today I will show you a simple way to link values from multiple rows to a single column on a DB2 SQL DBMS. In my sample table I listed the Formula 1 Championship world champions of the last 10 years. The table contains one entry for each year.

For this example the year value is defined as VARCAHR as well. The schema is VMBASE.

(tSource)

cYear	cChampion
1997	Jacques Villeneuve
1998	Mika Häkkinen
1999	Mika Häkkinen
2000	Michael Schumacher
2001	Michael Schumacher
2002	Michael Schumacher
2003	Michael Schumacher
2004	Michael Schumacher
2005	Fernando Alonso
2006	Fernando Alonso

What I want is to generate the following view:

(tDestination)

cDriver	cWonChamps
Fernando Alonso	2005, 2006
Jacques Villeneuve	1997
Michael Schumacher	2000, 2001, 2002, 2003, 2004
Mika Häkkinen	1998, 1999

To realise this I created the following function:

CREATE FUNCTION GET_WONYEARS(s_driver VARCHAR(254)) LANGUAGE SQL RETURNS VARCHAR(254) BEGIN ATOMIC DECLARE s_years VARCHAR(254); SET s_years = ''; for1: FOR tmp_row AS ( SELECT tSource.cYear AS cYear FROM tSource WHERE tSource.cChampion = s_driver ORDER BY tSource.cYear ) DO IF s_years = '' THEN SET s_years = tmp_row.cYear; ELSE SET s_years = s_years || ', ' || tmp_row.cYear; END IF; END FOR for1; RETURN s_years; END;

The call looks like the following:

SELECT DISTINCT
tSource.cChampion AS cDriver,
vmbase.GET_WONYEARS(tSource.cChampion) AS cWonChamps
FROM
tSource
ORDER BY

Courtesy