Create a concatenation function in SQL

Question

Hi,

Suppose I have a table a with one column b and three rows(1,2,3), I would like to create a function that will return '1,2,3' that would be called like this : SELECT FUNC(f), ... FROM ...

In other words, I have a linked table that have more than one rows linked to each rows of the first table and would like to concatenate the content of one column from the second table. In this case, it's a list of names associated with a specific observation.

I was thinking of using a SQL function for that, but I can't remember how... :(

Thanks

Answer 1

you can use COALESCE to convert values in one column to csv

http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

I am not sure how you will be able to create a function unless you do not mind creating a dynamic sql which can accept a column name and then build sql accordingly at run time.

edit. this works for SQL Server only. didn't realize that you have not mentioned any db.

Answer 2

DECLARE @list AS varchar(MAX)

SELECT @list = ISNULL(@list + ',', '') +  b
FROM MyTable

SELECT @list AS Result

Here a way to do this recursive (ms sql)

Answer 3

Depends on your technology.

If it is Oracle, check out the stragg function.
http://www.sqlsnippets.com/en/topic-11591.html

If it is MSSQL use the XML PATH trick
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2009/03/29/creating-an-output-csv-using-for-xml-and-multiple-rows.aspx

Answer 4

Here is an example for SQL Server:

CREATE FUNCTION ConcatenateMyTableValues
(@ID int)
RETURNS varchar(max)
AS
BEGIN
    declare @s as varchar(max);
    select @s = isnull(@s + ',', '') + MyColumn from MyTable where ID = @ID;
    return @s
end

And then you could use it like this:

select t.ID, t.Name, dbo.ConcatenateMyTableValues(t.ID)
from SomeTable t

Answer 5

I guess I'm going to answer my own question since I actually got a way to do it using CURSOR (that keyword I was missing in my thoughs..)

ALTER FUNCTION [dbo].[GET_NOM_MEDECIN_REVISEURS] (@NoAs810 int)

RETURNS NVARCHAR(1000)

AS
BEGIN
    IF @NoAs810 = 0
        RETURN ''
    ELSE
    BEGIN
        DECLARE @NomsReviseurs NVARCHAR(1000)
        DECLARE @IdReviseurs AS TABLE(IdReviseur int)
        DECLARE @TempNomReviseur NVARCHAR(50)   
        SET @NomsReviseurs = ''
        DECLARE CurReviseur CURSOR FOR 
            SELECT DISTINCT Nom FROM T_Ref_Reviseur R INNER JOIN T_Signature S ON R.IdReviseur = S.idReviseur WHERE NoAs810 = @NoAs810

        OPEN CurReviseur

        FETCH FROM CurReviseur INTO @TempNomReviseur
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @NomsReviseurs = @NomsReviseurs + @TempNomReviseur
            FETCH NEXT FROM CurReviseur INTO @TempNomReviseur

            IF @@FETCH_STATUS = 0
                SET @NomsReviseurs = @NomsReviseurs + ' - '
        END

        CLOSE CurReviseur

        RETURN @NomsReviseurs
    END

    RETURN ''       
END