Help with recursive query

Question

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.

You're going to have to be more descriptive if you want an answer. — Dave Markle, Dec 27 '08 at 15:12
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 — Rahul Jain, Dec 27 '08 at 15:39
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. — Rahul Jain, Dec 27 '08 at 15:44

score 3 · Answer 1 · answered Dec 27 '08 at 15:28

3

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

Converting Multiple Rows into a CSV String

answered Dec 27 '08 at 15:28

Turnkey

9,266
3
27
36

score 2 · Answer 2 · edited May 23 '17 at 12:01

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:

PIVOT in sql 2005

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)

AntiSanta - Is it possible to concatenate all those values coming in different columns, into one column. — Rahul Jain, Dec 27 '08 at 17:21
Take a look at Turnkey's answer, that seems to do the trick: http://stackoverflow.com/questions/395075/help-with-recursive-query#395090 — Kev, Dec 30 '08 at 13:27

score 1 · Accepted Answer · answered Dec 28 '08 at 21:06

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.

Vigya · Answer 4 · 2016-06-15T00:00:44.203

0

create table #a(DocNum int,  DocEntry int)

insert into #a
select 1,234 union all
select 2,324 union all
select 2,746 union all
select 3,876 union all
select 3,764 union all
select 4,100 union all
select 4,387 


select
    DocNum,
    stuff((
        select ',' + convert(varchar(25),t.DocEntry)
        from #a t
        where t.DocNum = t1.DocNum
        order by t.DocEntry
        for xml path('')
    ),1,1,'') as name_csv
from #a t1
group by DocNum
;

Output

DocNum name_sv

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

edited Jun 15 '16 at 00:00

answered Jun 14 '16 at 22:57

Vigya

122
1
7

I'd suggest to add a few words in text as to why and how this is an answer. Just makes it easier to understand. – Jun 14 '16 at 23:17

Help with recursive query

4 Answers4

Output