Easiest way to go from small single column table to single delimited string

Question

Was planning to put the following in a stored procedure but I now feel it lends itself better to being a udf.

It takes a small table like the following:

enter image description here

And returns a string like this:

a@b.c; b@c.d; c@d.e

How do I get rid of the temp table from the following so I can convert it to a function?

CREATE FUNCTION dbo.udf_emailString
(
    @x  NVARCHAR(50) = NULL
)
RETURNS VARCHAR(2000)
BEGIN
SET NOCOUNT ON;


--1.get all the related email addresses connected with @x and @y
WITH allAddresses(Email)
    AS
    (
    SELECT  Email
    FROM    WH.xxx.vw_Permissions
    WHERE   IndReceiveMail = 1 AND
            X = COALESCE(@x,X)
    GROUP BY Email
    )
, allAddressesRn(Email, rn)
    AS
    (
    SELECT  Email,
            ROW_NUMBER() OVER(ORDER BY Email)
    FROM    allAddresses
    ) 
SELECT  *
INTO    #EmailList
FROM    allAddressesRn;

--2.connect all of the above together seperating with semi-colons
DECLARE @fullAddress VARCHAR(2000) ='';
DECLARE @loopControl INT  = 1; 
WHILE @loopControl <= (SELECT MAX(rn) FROM #EmailList)
    BEGIN

        SET @fullAddress = 
            @fullAddress +  
                (
                SELECT  Email + '; '
                FROM    #EmailList
                WHERE   rn = @x
                );

    SET @loopControl = @loopControl + 1;
    END;

RETURN @fullAddress;

END;

EDIT

With the comments and answer this seems to be a much more compact approach:

CREATE FUNCTION dbo.udf_emailString
(
    @x  NVARCHAR(50) = NULL
)
RETURNS VARCHAR(2000)
BEGIN


DECLARE @fullAddress VARCHAR(2000) ='';

SELECT @fullAddress = 
STUFF(
        (
    select '; ' + Email as [text()]
    FROM    
            (
            SELECT  Email
            FROM    WH.xxx.vw_Permissions
            WHERE   IndReceiveMail = 1 AND
                    X = COALESCE(@x,X)
            GROUP BY Email
            ) x
        for xml path ('')
)
,1,1,'');

SET @fullAddress = @fullAddress + ';';
RETURN @fullAddress;

END;

Have to looked at STUFF/FOR XML PATH to create the concatenated list? I don't see why you are using a WHILE LOOP to create this. Or any of the other methods listed in this question http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Taryn, Sep 09 '13 at 20:12
@bluefeet thanks bluefeet - I'll edit OP with code using `STUFF/FOR XML PATH` — whytheq, Sep 09 '13 at 21:35

score 1 · Accepted Answer · edited Sep 10 '13 at 06:46

1

As bluefeet suggested use STUFF():

select @fullAddress = stuff(
        (select ';' + Email as [text()]
        from #EmailList
        for xml path (''))
        ,1,1,'')

EDIT: Sorry, I ment to illustrate the STUFF function and it was easier to test on temp table :). Your whole udf would be:

CREATE FUNCTION dbo.udf_emailString
(
  @x  NVARCHAR(50) = NULL
)
RETURNS VARCHAR(2000)
BEGIN

--1.get all the related email addresses connected with @x and @y
WITH allAddresses(Email)
AS
(
SELECT  Email
FROM    WH.xxx.vw_Permissions
WHERE   IndReceiveMail = 1 AND
        X = COALESCE(@x,X)
GROUP BY Email
)
select @fullAddress = stuff(
    (select ';' + Email as [text()]
    from allAddresses
    for xml path (''))
    ,1,1,'');

 RETURN @fullAddress;

 END;

edited Sep 10 '13 at 06:46

whytheq

34,466
65
172
267

answered Sep 09 '13 at 21:13

user2065377

448
3
12

+1 thanks for implementing Bluefeets suggesting (...even if it still contains a temp table that won't work within a function :)) – whytheq Sep 09 '13 at 21:34
`ROWNUMBER` was in the original as it was used in the `WHILE` loop ....I've edited it out of your answer. – whytheq Sep 10 '13 at 06:47

Easiest way to go from small single column table to single delimited string

1 Answers1