Sql function to append column

Question

Hi,

Lets say I have a table that looks like this:

Name      value  
1 Ford    "some text here and there."    
2 Honda   "More Text Again"   

How can I write a sql function that will append me the 'value' column of all of the rows. so the result would be

sometext here and there. More Text Again

also it would be nice if I can specify a separator.

Answer 1

Here's a function that does that. In this case the separator is ,:

CREATE FUNCTION  AppendColumn (@ItemID int)

    RETURNS varchar (8000)
AS
BEGIN
    DECLARE @AppendedText   varchar (8000)
    SET     @AppendedText   = NULL  -- MUST be null to avoid leading comma.

    SELECT
        @AppendedText       =  COALESCE (@AppendedText + ', ', '') + value
    FROM
        YourTable
    WHERE
        ... ...
    ORDER BY
        ... ...

    RETURN @AppendedText
END

Answer 2

You can cheat using FOR XML PATH if you are using 2005+:

SELECT Value + ','
FROM table
FOR XML PATH('')

Answer 3

Concatenate the values together in a variable, adding a separator, using a statement like this:

SELECT @result = @result + t.Value + @separator
FROM @test AS t;

As a full example:

--** Declare test table
DECLARE @test TABLE (Name VARCHAR(20), Value VARCHAR(50));

--** Insert test data
INSERT INTO @test (Name, Value)
SELECT 'Ford', 'some text here and there.' UNION ALL
SELECT 'Ford', 'More Text Again'

--** Declare variables
DECLARE @result VARCHAR(1000);
DECLARE @separator VARCHAR(3);

--** Set separator and initialize the result (important)
SET @result = '';
SET @separator = ' | ';

--** Concatente the values together separating them with @separator
SELECT @result = @result + t.Value + @separator
FROM @test AS t;

--** Return the result removing the final separator
SELECT LEFT(@result, LEN(@result)-1);

This uses | as the separator and will give you:

some text here and there. | More Text Again