lundi 4 juillet 2016

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.

Aucun commentaire:

Enregistrer un commentaire