INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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.

Jobs

Rework process not to use cursor

Rework process not to use cursor

(OP)
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
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)


RE: Rework process not to use cursor

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

CODE

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

(OP)
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."

--Greg

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!

Resources

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