Monday, January 25, 2010

Comma Seprate String


DECLARE @Table TABLE (ID1 INT, ID2 INT, ID3 INT, Name VARCHAR(10))

INSERT INTO @Table VALUES (1,1,1,'a')
INSERT INTO @Table VALUES (1,1,1,'b')
INSERT INTO @Table VALUES (2,1,1,'a')
INSERT INTO @Table VALUES (2,1,1,'b')
INSERT INTO @Table VALUES (3,1,1,'a')
INSERT INTO @Table VALUES (3,1,1,'b')

SELECT * FROM @Table

SELECT
DISTINCT
ID1
, ID2
, ID3
, REPLACE(
REPLACE(
REPLACE(
CAST((
SELECT Name
FROM @Table AS B
WHERE b.ID1 = a.ID1
AND b.ID2 = a.ID2
AND b.ID3 = a.ID3
FOR XML PATH('')
) AS VARCHAR(MAX) )
, ''
, ', '
)
, ''
, ''
)
, ''
, ''
) AS Name
FROM @Table AS A

SELECT
DISTINCT
ID1
, ID2
, ID3
, REPLACE(
(
SELECT Name AS 'data()'
FROM @Table AS B
WHERE b.ID1 = a.ID1
AND b.ID2 = a.ID2
AND b.ID3 = a.ID3
FOR XML PATH('')
)
, ' '
, ', '
) AS Name
FROM @Table AS A

SELECT STUFF((
SELECT ',' + s.Name
FROM @Table s
ORDER BY s.Name
FOR XML PATH('')
),1,1,'') AS CSV


SELECT Name AS 'data()'
FROM @Table
FOR XML PATH('')

No comments: