CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Jim
Jim

Reputation: 1

Stored procedure to concatenate rows of a table

I have a table called name, which is very small and only stores name and age.

Name    Age
-------------
Alex     3
Laura    2
Kate     2
Max      3

I need to write a stored procedure that can concatenate the rows of this table in to one string. I've seen examples around but none that really help me here.

The string needs to be returned as "Alex,3;Laura,2;Kate,2;Max,3;".

I figured an easy way to do this would be to join the columns in to one, to add the comma and then store the results in a temp table to later use:

select Name + ',' + cast(Age as varchar(3)) as both
into #temptable
from NameAge

Then I was joining each row and separating with the semi-colon:

DECLARE @Names VARCHAR(8000)  
SELECT @Names = COALESCE(@Names + ';', '') + both FROM #temptable
SELECT @Names

Which returns the correct results, however I obviously can't run it one after the other if I make a change because the temp table has already been created.

I basically need to use these statements inside a stored procedure but I'm having a little bit of trouble doing this.

Any help would be appreciated or links to something similar as all my searches haven't turned up quite what I'm looking for.

Upvotes: 0

Views: 1429

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

select
    stuff((select ';' + Name + ',' + convert(varchar(255), Age)
                from [name]
                for xml path(N''), type).value(N'.[1]', N'nvarchar(max)')
            ,1, 1, '')

Or using your original solution:

DECLARE @Names VARCHAR(8000)  
SELECT @Names = COALESCE(@Names + ';', '') + Name + ',' + cast(Age as varchar(3)) FROM [name]
SELECT @Names

Upvotes: 3

Related Questions