CODEWITHSUNDEEP
sqlsql-servercommon-table-expressionrecursive-query
Rahul Jain
Rahul Jain

Reputation: 195

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.

Upvotes: 1

Views: 1046

Answers (4)

Vigya
Vigya

Reputation: 142

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

Upvotes: 0

Thuglife
Thuglife

Reputation: 404

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.

Upvotes: 1

Kev
Kev

Reputation: 119806

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

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)

Upvotes: 2

Turnkey
Turnkey

Reputation: 9406

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

Converting Multiple Rows into a CSV String

Upvotes: 3

Related Questions