CODEWITHSUNDEEP
sql-serverxml
Dah Sra
Dah Sra

Reputation: 4425

How to concatenate multiple rows values of a column into single column in sql without xml path

Hi i am trying concatenate values of multiple rows in a column to a single column . I can achieve it by using FOR XML PATH('') .But is there any alternative solution for concatenating in sql server.

StudentName
-------------
Mary
John
Sam

and the expected output should be like

StudentName
-------------
Mary, John, Sam

by using this i can achieve 

select Name + ', ' AS 'data()'  from NAMES FOR XML PATH('')

but without using XML PATH('') is there any alternatives..?

Upvotes: 0

Views: 177

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

One solution I'd not advise normally (I'd prefer the XML PATH solution):

DECLARE @Result VARCHAR(MAX)='';
SELECT @Result = @Result + ',' + Name
FROM NAMES;

SELECT SUBSTRING(@Result,2,10000)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can concatenate names using recursive CTEs:

with s as (
      select s.*, row_number() over (order by studentname) as seqnum
      from students
     ),
     cte as (
      select studentName as names, seqnum
      from s
      where seqnum = 1
      union all
      select cte.names + ',' + s.studentName
      from cte join
           s
           on s.seqnum = cte.seqnum + 1
     )
select top 1 cte.names
from cte
order by seqnum desc;

Upvotes: 1

Related Questions