Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Rework process not to use cursor

Rework process not to use cursor

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:

CODE --> sql

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
		if @FirstIteration = 0 
					   set @PrevPolicyNo = @PolicyNo
					   set @FirstIteration = 1

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

		 set @PrevPolicyno = @Policyno

		FETCH NEXT FROM MyCursor INTO  @PolicyNo, @days
 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)

RE: Rework process not to use cursor

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.

RE: Rework process not to use cursor

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

RE: Rework process not to use cursor

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."


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close