CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
mmssaann
mmssaann

Reputation: 1507

concatinate all rows of a column into single value

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...

Upvotes: 1

Views: 116

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

DECLARE @names NVARCHAR(MAX)

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

print @names

Upvotes: 4

Devart
Devart

Reputation: 121912

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

Upvotes: 3

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this:

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

Upvotes: 5

Related Questions