How to replace the cursor
-
please take a look at the below SQL CODE
---
create table mytable
(
potype varchar(10)
)
insert into mytable values
('AA'),
('BB'),
('CC')
declare @potypestring varchar(50)=''
declare @potype varchar(10)
declare mytype cursor local for
select potype from mytable
open mytype
fetch next from mytype into @potype
while @@FETCH_STATUS=0
begin
set @potypestring+=','+@potype
fetch next from mytype into @potype
end
deallocate mytype
select SUBSTRING(@potypestring,2,LEN(@potypestring)-1)
---
it will return a string, converting a set-based to a format string. and it also warried me a long time.
how to remove the cursor function and replaced by other useful function?
Can any friend give me some tips or good suggestion? Thanks!
-
Hey there.. try this:
declare @mytable table
(
id int,
potype varchar(10)
)
insert into @mytable values (1,'AA')
insert into @mytable values (1,'BB')
insert into @mytable values (1,'CC')
SELECT p1.id,
STUFF ( ( SELECT ','+potype
FROM @mytable p2
WHERE p2.id = p1.id
ORDER BY potype
FOR XML PATH('')
) , 1,1,SPACE(0)) AS Concat_Values
FROM @mytable p1
GROUP BY p1.id ;
For betterment of the code, i have included the id column..as i cant see what you want exactly from your cursor, i am just giving a trial based code.. if could explaing much more of the desired output, i can help you further 🙂
Tell us if that is what you seeked 🙂
-
Get it, Thank you so much,ColdCoffee:-)
I have a function need to trasnfer the set-based result to a format string. I use the database cursor rotating and processing it one by one, but the performance is very low, so get the good idea from you. FOR XML PATH and STUFF and achieve the very good result.
Thanks!:-P
-
Not a problem , changebluesky, you are welcome! 🙂
-
changbluesky (5/31/2010)
please take a look at the below SQL CODE---
create table mytable
(
potype varchar(10)
)
insert into mytable values
('AA'),
('BB'),
('CC')
declare @potypestring varchar(50)=''
declare @potype varchar(10)
declare mytype cursor local for
select potype from mytable
open mytype
fetch next from mytype into @potype
while @@FETCH_STATUS=0
begin
set @potypestring+=','+@potype
fetch next from mytype into @potype
end
deallocate mytype
select SUBSTRING(@potypestring,2,LEN(@potypestring)-1)
---
it will return a string, converting a set-based to a format string. and it also warried me a long time.
how to remove the cursor function and replaced by other useful function?
Can any friend give me some tips or good suggestion? Thanks!
Hope this might help you 🙂
declare @mytable table
(
potype varchar(10)
)
insert into @mytable values ('AA')
insert into @mytable values ('BB')
insert into @mytable values ('CC')
declare @potypestring varchar(50)
select @potypestring = isnull(@potypestring + ',' , '' ) + isnull( potype , '' )
from @mytable
select @potypestring
Nag
Nag
------------------------------------------------
How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537
-
Nagesh S-432384 (5/31/2010)
changbluesky (5/31/2010)
please take a look at the below SQL CODE---
create table mytable
(
potype varchar(10)
)
insert into mytable values
('AA'),
('BB'),
('CC')
declare @potypestring varchar(50)=''
declare @potype varchar(10)
declare mytype cursor local for
select potype from mytable
open mytype
fetch next from mytype into @potype
while @@FETCH_STATUS=0
begin
set @potypestring+=','+@potype
fetch next from mytype into @potype
end
deallocate mytype
select SUBSTRING(@potypestring,2,LEN(@potypestring)-1)
---
it will return a string, converting a set-based to a format string. and it also warried me a long time.
how to remove the cursor function and replaced by other useful function?
Can any friend give me some tips or good suggestion? Thanks!
Hope this might help you 🙂
declare @mytable table
(
potype varchar(10)
)
insert into @mytable values ('AA')
insert into @mytable values ('BB')
insert into @mytable values ('CC')
declare @potypestring varchar(50)
select @potypestring = isnull(@potypestring + ',' , '' ) + isnull( potype , '' )
from @mytable
select @potypestring
Nag
Please put that into a function and try it against a million rows. You too will make the change to the XML method. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I 100% agree with you Jeff, my solution was for limited number of records not for huge data.
Nag
Nag
------------------------------------------------
How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks for giving me so much good methods! I learned more from you:-P
-
Nagesh S-432384 (5/31/2010)
I 100% agree with you Jeff, my solution was for limited number of records not for huge data.Nag
How many times have you been told that some code will only be run against a limited number of records... and then months later find out it's the cause of a major performance problem running against hundreds / thousands of times the number of records expected? Always code as if you're running against a million rows and it's not likely that you'll face this situation.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (6/1/2010)
Nagesh S-432384 (5/31/2010)
I 100% agree with you Jeff, my solution was for limited number of records not for huge data.Nag
How many times have you been told that some code will only be run against a limited number of records... and then months later find out it's the cause of a major performance problem running against hundreds / thousands of times the number of records expected? Always code as if you're running against a million rows and it's not likely that you'll face this situation.
Gosh... you must be my clone. I almost posted something nearly the same word for word and decided against it because people just don't get it. My post ended with "if you're not testing against a million rows, you're not testing." 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (6/2/2010)
WayneS (6/1/2010)
Nagesh S-432384 (5/31/2010)
I 100% agree with you Jeff, my solution was for limited number of records not for huge data.Nag
How many times have you been told that some code will only be run against a limited number of records... and then months later find out it's the cause of a major performance problem running against hundreds / thousands of times the number of records expected? Always code as if you're running against a million rows and it's not likely that you'll face this situation.
Gosh... you must be my clone. I almost posted something nearly the same word for word and decided against it because people just don't get it. My post ended with "if you're not testing against a million rows, you're not testing." 😀
Hi Wayne and Jeeff,
Sorry for my post, I will not post such quires again, I used it in some of my quires where i was using where condition while fetching the data and the data was never crossing more than 200 rows, I just did some modifications and posted the query by hoping he also need to query few rows of data. I completely apology for it and thanks a lot for both of you for giving some great tips..
Nag
Nag
------------------------------------------------
How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537
-
Very cool feedback, Nagesh. Thank you for taking the time.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (6/2/2010)
Very cool feedback, Nagesh. Thank you for taking the time.one more late response Jeff 🙂 I am in IST (in India) so I will be always late :-).
Nag
Nag
------------------------------------------------
How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537
-
Nagesh S-432384 (6/3/2010)
Jeff Moden (6/2/2010)
Very cool feedback, Nagesh. Thank you for taking the time.one more late response Jeff 🙂 I am in IST (in India) so I will be always late :-).
Nag
I mean to say my response will be late 😉
I am a big follower of you Jeff, thanks for you're great contribution here in SSC, I have learned a lot here from great people like you. Thanks once again for all SSC members.
Nag
Nag
------------------------------------------------
How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537
-
ColdCoffee (5/31/2010)
Hey there.. try this:declare @mytable table
(
id int,
potype varchar(10)
)
insert into @mytable values (1,'AA')
insert into @mytable values (1,'BB')
insert into @mytable values (1,'CC')
SELECT p1.id,
STUFF ( ( SELECT ','+potype
FROM @mytable p2
WHERE p2.id = p1.id
ORDER BY potype
FOR XML PATH('')
) , 1,1,SPACE(0)) AS Concat_Values
FROM @mytable p1
GROUP BY p1.id ;
For betterment of the code, i have included the id column..as i cant see what you want exactly from your cursor, i am just giving a trial based code.. if could explaing much more of the desired output, i can help you further 🙂
Tell us if that is what you seeked 🙂
Slight caveat, the query doesn't handle special XML characters, a simple mod fixes it.
declare @mytable table(
id int,
potype varchar(10)
)
insert into @mytable values (1,'A>A')
insert into @mytable values (1,'B&B')
insert into @mytable values (1,'C<C')
SELECT p1.id,
STUFF ( ( SELECT ','+potype
FROM @mytable p2
WHERE p2.id = p1.id
ORDER BY potype
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values
FROM @mytable p1
GROUP BY p1.id ;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply