concatinate all rows of a column into single value

Question

I have table called Rule.

RuleId    Name
1          A1
2          A2
3          A3
.
.
.

Now I want all the names as single result.

may be like @allnames = A1,A2,A3

Can somebody advise how to write query for this without using loops?

Thanks in advance...

duplicate of http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Rex, Sep 02 '13 at 08:29

score 5 · Accepted Answer · answered Sep 02 '13 at 08:29

5

Try this:

SELECT @allnames = STUFF((SELECT distinct ',' + Name
                      FROM table1
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SQL Fiddle Demo

answered Sep 02 '13 at 08:29

Mahmoud Gamal

78,257
17
139
164

t-clausen.dk · Answer 2 · 2013-09-02T09:11:55.420

4

DECLARE @names NVARCHAR(MAX)

SELECT @names = coalesce(@names + ',', '') + coalesce(Name, '') 
FROM (SELECT distinct Name FROM Rule) x

print @names

edited Sep 02 '13 at 09:11

answered Sep 02 '13 at 08:46

t-clausen.dk

43,517
12
59
92

1

This appends duplicates. anyway to get distinct ? – mmssaann Sep 02 '13 at 08:51
@mmssaann sure, although it is not specified in the question – t-clausen.dk Sep 02 '13 at 09:17

score 3 · Answer 3 · answered Sep 02 '13 at 08:30

Try this one -

DECLARE @temp TABLE ([RuleId] INT, Name CHAR(2))
INSERT INTO @temp([RuleId], Name)
VALUES
    (1, 'A1'),
    (2, 'A2'),
    (3, 'A3')

DECLARE @all_names NVARCHAR(MAX)

SELECT @all_names = STUFF((
    SELECT DISTINCT ',' + Name
    FROM @temp
    --ORDER BY Name
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SELECT @all_names

Output -

---------------
A1,A2,A3

concatinate all rows of a column into single value

3 Answers3