Rahul Jain Rahul Jain - 5 months ago 11
SQL Question

Help with recursive query

I've following problem, which I've not been able to do successfully. Your help will be appreciated. I'm using SQL 2005, and trying to do this using CTE.

Table has following 2 columns

DocNum DocEntry
1 234
2 324
2 746
3 876
3 764
4 100
4 387


Expected result is as follows

1 234
2 324, 746
3 876, 764
4 100, 387


Thanks
Rahul Jain




Further explanation transcribed from the comments:

I'm using a query like following:

WITH ABC (DocNum, DocEntry) AS
(SELECT DocNum, Cast(DocEntry As VARCHAR(8000))
FROM Temp5
WHERE DocNum = 1
UNION ALL
SELECT a.DocNum, A.DocEntry + ', ' + B.DocEntry
FROM ABC B INNER JOIN Temp5 A ON B.DocNum +1= A.DocNum
WHERE A.DOCNUM > 1)
SELECT * FROM ABC;


Result from above query is as follows

1 234
2 234, 324
2 234, 746
3 234, 746, 876
3 234, 746, 764


I dont want those numbers to repeat as shown in question.

Answer
SELECT 
	DocNum,
	STUFF((SELECT ', ' + CAST(DocEntry AS VARCHAR(MAX)) AS [text()]
		FROM Temp5 b
		WHERE a.DocNum = b.DocNum
		FOR XML PATH('')), 1, 2, '') AS DocEntry
FROM Temp5 a
GROUP BY DocNum

Itzik Ben-Gan in his excellent book T-SQL QUERYING has some specialized solutions for aggregate string concatenation. The query screams for itself.

Comments