Concatenation of Rows

See more:

SQL-Server-2005

SQL-Server

Hi,

I have a problem when I try to concatenate few rows. The program just keep adding the same data the number of times that it exist. For example if I want to concatenate 6 rows into 1, and each rows contain something like this:

Alabama : Huntsville
California : Los Angeles
Miami : Florida
Michigan : Detroit
Ohio : Cleveland
Texas : Houston

(This is not the real data is just an example)
It is doing the following:
Row State:Capital
1 Alabama : Huntsville, California : Los Angeles, Miami : Florida,
Michigan : Detroitm, Ohio : Cleveland, Texas : Houston, Alabama :
Huntsville, California : Los Angeles, and so on...

I am using CROSS APPLY, an using as example this:

SELECT DISTINCT CategoryId, ProductNames
FROM Northwind.dbo.Products p1
CROSS APPLY ( SELECT ProductName + ',';
FROM Northwind.dbo.Products p2
WHERE p2.CategoryId = p1.CategoryId
ORDER BY ProductName
FOR XML PATH('') ) D ( ProductNames )