CODEWITHSUNDEEP
sqlsql-server-2008-r2
Lajja Thaker
Lajja Thaker

Reputation: 2041

fetch comma separated value in SQL query

Please check below table 

Code   Name  
-----------
 001   A
 001   B

My query is 

Select Code,Name from TableA

But I need records like 

Code  Name
------------
001    A,B

How can I do that?

Upvotes: 2

Views: 136

Answers (2)

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56727

You could use COALESCE. The following sample turns this

Hello

World

It's

Me

into this

Hello, World, It's, Me

DECLARE @test NVARCHAR(2000)
SELECT @test = COALESCE(@test + ', ', '') + Field FROM Sampletable WHERE … AND Field IS NOT NULL
SELECT @test

You should be able to adapt this to your sample.

Upvotes: 0

John Woo
John Woo

Reputation: 263933

Unlike MySQL, SQL Server doesn't have a built-in function for this but you can still simulate it by using CROSS APPLY and FOR XML PATH('')

SELECT  a.Code, 
        SUBSTRING(d.[Name],1, LEN(d.[Name]) - 1) AddressList
FROM    
        (
            SELECT DISTINCT Code
            FROM TableA
        ) a
        CROSS APPLY
        (
            SELECT [Name] + ', ' 
            FROM TableA AS B 
            WHERE A.[Code] = B.[Code] 
            FOR XML PATH('')
        ) D ([Name]) ;

SQLFiddle Demo

Upvotes: 3

Related Questions