SQL result as single string

[ykc73]

Hi,
I am trouble in writing and SQL that returns the result of an SQL as a concatenated single string. Is there an aggregate grouping function on string in DB2?

Ex:
Table: Employee
FirstName LastName Address
John Smith 123 Smith St
Barry Gray 123 Gray St

SQL: Select FirstName from Employee


This returns all the rows in the Employee table with just the FirstName Column. Instead of multiple rows, I want to return just one string with all the values of the rows concatenated and seperated by ",". Can this be done in DB2 procedure or SQL?

Desired Result: "John, Barry"

Thanks in advance

ykc

 

[JarlH]

SELECT statements return rows.

If you want one single string containing data from all rows, you'll have to write some kind of procedure. Either a DBMS stored procedure or in your application.

 

[rac2]

SQL deals with subsets of relations. Given the n-tuples by a RDBMS, applications format them for display. Not withstanding the existence of formatting functions within various implementations such as DB2. You might do better to post in the DB2 forum.

If you were using Microsoft SQL Server you might be interested in this

DECLARE @s VARCHAR(1000)
SELECT @s = COALESCE(@s+',','') + FirstName FROM Saints

SELECT @s

With thanks to Nigel Rivett

http://www.nigelrivett.net/index.html