group_concat in sql server

Posted by Murugavelmsc under Interview Questions on 6/15/2013 | Points: 10 | Views : 2097 | Status : [Member] | Replies : 3
Experts,

I need a query to convert the particular columns into single column.

Eg.
sno name
1 AA
2 BB
3 CC
4 DD
5 EE

output

name
AA,BB,CC,DD,EE

Thanks,
Murugavel S

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/



Responses

Posted by: Niladri.Biswas on: 6/16/2013 [Member] Platinum | Points: 25

Up
0
Down
Declare @t table(SNo int identity,name varchar(50))

Insert Into @t
select 'AA' Union All select 'BB' Union All select 'CC' Union All
select 'DD' Union All select 'EE'

Select Name = stuff((Select ',' + CAST(name as varchar(100))
From @t
for xml path('')),1,1,'')


output
Name

AA,BB,CC,DD,EE


Best Regards,
Niladri Biswas

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.Biswas on: 6/16/2013 [Member] Platinum | Points: 25

Up
0
Down
Declare @t table(SNo int identity,name varchar(50))

Insert Into @t
select 'AA' Union All select 'BB' Union All select 'CC' Union All
select 'DD' Union All select 'EE'

Declare @assignTo nvarchar(4000)
select @assignTo = Coalesce(@assignTo + ',', '') + CAST(Name as nvarchar(250))
from @t
select Name = @assignto

output

Name

AA,BB,CC,DD,EE


Best Regards,
Niladri Biswas

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 6/17/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can use the below steps:-

1. Create a table Value and store all values to this table.
2. Below are the different queries:-

Query 1: Using "COALESCE" function

DECLARE @temp VARCHAR(MAX)
SELECT @temp = COALESCE(@temp+', ' ,'') + val
FROM [dbo].[Value]
SELECT @temp

Query 2: Using without "COALESCE" function

DECLARE @temp VARCHAR(MAX)
SET @temp = ''
SELECT @temp = @temp + val + ', '
FROM [dbo].[Value]
SELECT SUBSTRING(@temp, 0, LEN(@temp))

Query 3: Using "FOR XML PATH"

DECLARE @temp VARCHAR(MAX)
SET @temp = (SELECT ', ' + cast(s.val as varchar)
FROM [dbo].[Value] s
ORDER BY s.state
FOR XML PATH(''))
SELECT SUBSTRING(@temp, 2, 200000) AS state

Hope this will help you.


Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response