Rework process not to use cursor 2

[SaltyTheFrog]

I need to string multiple rows into a single row with a key column and dynamic entries. I cobbled something together using a cursor but I was hoping there is a better way. I'm on SQL 2005 set to 2000 SQL compatibility so I can't use some of the newer stuff.

Here is the data:

If object_id('tempdb..##Temp') is not null drop table ##Temp

Create table ##Temp(PolicyNo int, Days int)
insert into ##Temp(PolicyNo,Days) Values(488,9)
insert into ##Temp(PolicyNo,Days) Values(488,279)
insert into ##Temp(PolicyNo,Days) Values(2206,22)
insert into ##Temp(PolicyNo,Days) Values(2863,20)
insert into ##Temp(PolicyNo,Days) Values(6669,9)
insert into ##Temp(PolicyNo,Days) Values(6669,18)
insert into ##Temp(PolicyNo,Days) Values(6669,25)

Here is the desired output 
PolicyNo Days
488      9, 279
2206     22
2863     20
6669     9, 18, 25

And here is what I did:

Declare @PolicyNo int
Declare @DaysCount Varchar(200)
Declare @Days int
Declare @PrevPolicyNo int
Declare @FirstIteration int

set @FirstIteration = 0
set @dayscount = ''

DECLARE MyCursor CURSOR FOR SELECT policyno, days FROM ##Temp where days > 0 order by policyno
Open MyCursor
FETCH NEXT FROM MyCursor INTO @PolicyNo, @days
WHILE (@@FETCH_STATUS <> -1)
BEGIN
		if @FirstIteration = 0 
				begin  
					   set @PrevPolicyNo = @PolicyNo
					   set @FirstIteration = 1
				end

		if @Policyno = @prevPolicyno
				begin
						set @DaysCount = @DaysCount + convert(varchar,isnull(@days,'@days')) + ','
				end
		else
				begin
					  Print 'Policyno ' + convert(varchar,@PrevPolicyno)   +  ' days count  ' + convert(varchar,@dayscount)
						set @DaysCount = ''
						set @DaysCount = @DaysCount + convert(varchar,isnull(@days,'@days')) + ','
				end 

		 set @PrevPolicyno = @Policyno

		FETCH NEXT FROM MyCursor INTO  @PolicyNo, @days
END
 Print 'Policyno ' + convert(varchar,@PrevPolicyno)   +  ' days count  ' + convert(varchar,@dayscount)
Close MyCursor
Deallocate MyCursor

If object_id('tempdb..##Temp') is not null drop table ##Temp

Can somebody do this without a cursor? I played with PIVOT, STUFF and COALESCE but needing the policyno and the fact it needs to be dynamic ruled them out for me (probably from ignorance)

 

[DaveInIowa]

I'm not sure if the following "uses some of the newer stuff" or not. Are you able to use FOR XML PATH?

SELECT PolicyNo,
       STUFF((SELECT ',' + CAST(Days AS VARCHAR) FROM ##Temp WHERE PolicyNo = t.PolicyNo ORDER BY Days ASC FOR XML PATH('')), 1, 1, '') AS DaysList
  FROM ##Temp t
 GROUP BY PolicyNo

I included the ORDER BY Days ASC to show you can order your list if you'd like.

 

[SaltyTheFrog]

WOW...worked great and is sooo much faster! Thank you!

 

[gbaughma]

Good job Dave. Very elegant.



Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg