Help with recursive query

Question

Hi,

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 1

Here's an article that describes methods to do that:

Converting Multiple Rows into a CSV String

Answer 2

I don't think CTE's are the complete answer to your problem. What you're after is a PIVOT query where the number of columns in the PIVOT are unknown at query time. This question and answer looks like what you're after:

http://stackoverflow.com/questions/198716/pivot-in-sql-2005

http://stackoverflow.com/questions/198716/pivot-in-sql-2005#199763

From the example in the above answer, this is the SQL modified for your table (which I've named 'q395075' - so you just need to replace with your table name):

DECLARE @sql AS varchar(max)
DECLARE @pivot_list AS varchar(max) -- Leave NULL for COALESCE technique
DECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE technique

SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + ']'
        ,@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + '] AS [col_' + CONVERT(varchar, PIVOT_CODE) + ']'
FROM (
    SELECT DISTINCT PIVOT_CODE
    FROM (
        SELECT DocNum, DocEntry, ROW_NUMBER() OVER (PARTITION BY DocNum ORDER BY DocEntry) AS PIVOT_CODE
        FROM q395075
    ) AS rows
) AS PIVOT_CODES

SET @sql = '
;WITH p AS (
    SELECT DocNum, DocEntry, ROW_NUMBER() OVER (PARTITION BY DocNum ORDER BY DocEntry) AS PIVOT_CODE
    FROM q395075
)
SELECT DocNum, ' + @select_list + '
FROM p
PIVOT (
    MIN(DocEntry)
    FOR PIVOT_CODE IN (
        ' + @pivot_list + '
    )
) AS pvt
'

PRINT @sql

EXEC (@sql)

Answer 3

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.

Answer 4

This is a really good solution. Quite easy at that as well.

Thanks, Rahul