Convert Multiple Rows into One Comma Separated Values in SQL server

Views: 4415
Comments: 0
Like/Unlike: 2
Posted On: 13-May-2018 06:55 

Share:   fb twitter linkedin
Brian
2376 Points
13 Posts

Introduction

I have come across the problem converting multiple rows values to a comma separated values while doing development. Finally I have sorted this problem. So I have decided to write an blog on this. 

Problem

I have following table 'Keyword'

Id Keyword Detail
2 Java Java
3 JavaScript JavaScript

Now I want to convert Keyword row values to one string value.

Ex: Java,JavaScript, etc

Solutions

For the above problem, we can solve above problem with different queries.

Solution I): Using "COALESCE" function

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

Solution II): Using without "COALESCE" function

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

Solution III): Using "FOR XML PATH"

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

Conclusion
Hope this will help you to solve your problem.

0 Comments
 Log In to Chat