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.

``````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.

Source (Stackoverflow)