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?
Add a comment
|
2 Answers
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
Comments
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.
