I have a table that looks like the following
COLUMN_A|| COLUMN_B ||
======================
NAME1 || 1 ||
NAME2 || 1 ||
NAME3 || 1 ||
NAME4 || 2 ||
NAME5 || 2 ||
NAME6 || 3 ||
NAME7 || 3 ||
I want to concatenate COLUMN_A depending on COLUMN_B. If values of COLUMN_B are same, then COLUMN_A should be concatenated seperated by a comma.
Expected result
NAME1, NAME2, NAME3
NAME4, NAME5
NAME6, NAME7
Declare @Table table (id int,Name varchar(25))
Insert Into @Table values
(1,'NAME1'),
(1,'NAME2'),
(1,'NAME3'),
(2,'NAME4'),
(2,'NAME5'),
(3,'NAME6'),
(3,'NAME7')
SELECT
[ID],
STUFF((
SELECT ', ' + NAME
FROM @Table
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NAME
FROM @Table Results
GROUP BY ID
Returns
ID NAME
1 NAME1, NAME2, NAME3
2 NAME4, NAME5
3 NAME6, NAME7
You can use recursion to help with this solution:
CREATE TABLE #Temp(ColumnA VARCHAR(50), ColumnB INT)
INSERT INTO #Temp
VALUES('NAME1', 1)
, ('NAME2', 1)
, ('NAME3', 1)
, ('NAME4', 2)
, ('NAME5', 2)
, ('NAME6', 3)
, ('NAME7', 3)
;WITH Numbered
AS
(
SELECT ColumnA, ColumnB, ROW_NUMBER() OVER (PARTITION BY ColumnB ORDER BY ColumnA) AS RowNum
FROM #Temp
)
,RecordCounts
AS
(
SELECT ColumnB, COUNT(1) AS RecordCount
FROM #Temp
GROUP BY ColumnB
)
,RecursiveCTE
AS
(
SELECT ColumnB, CONVERT(VARCHAR(50), ColumnA) AS ColumnA, RowNum
FROM Numbered
WHERE RowNum = 1
UNION ALL
SELECT N.ColumnB, CONVERT(VARCHAR(50), R.ColumnA + ', ' + N.ColumnA), N.RowNum
FROM RecursiveCTE R
INNER JOIN Numbered N ON R.ColumnB = N.ColumnB
AND N.RowNum = R.RowNum + 1
)
SELECT ColumnA
FROM RecursiveCTE R
INNER JOIN RecordCounts RC ON R.ColumnB = RC.ColumnB
AND R.RowNum = RC.RecordCount
ORDER BY R.ColumnB
DROP TABLE #Temp
