Help with recursive query
I have the following problem that I was unable to successfully deal with. Your help will be highly appreciated. I am using SQL 2005 and am trying to do this with CTEs.
The table has the following 2 columns
DocNum DocEntry
1 234
2 324
2 746
3 876
3 764
4 100
4 387
The expected output looks like this
1 234
2 324, 746
3 876, 764
4 100, 387
Thank you Rahul Jain
Further explanation is transcribed from the comments:
I am using the following query:
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;
The result from the following query looks like this
1 234
2 234, 324
2 234, 746
3 234, 746, 876
3 234, 746, 764
I don't want these numbers to be repeated as shown in the question.
source to share
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, contains some specialized solutions for concatenating aggregate rows. The request screams for itself.
source to share
I don't think CTE is the complete answer to your problem. Why do you need a PIVOT query where the number of columns in the PIVOT is unknown at the time of the query. This question and answer looks like you:
From the example answer above, this is the SQL modified for your table (which I called "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)
source to share
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
source to share