How to replace the cursor

Question

Post reply

How to replace the cursor

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

DECLARE @mytable

TABLE (

id INTEGER NOT NULL,

potype VARCHAR(10) NOT NULL

);

INSERT @mytable VALUES (1,'A>A');

INSERT @mytable VALUES (1,'B&B');

INSERT @mytable VALUES (1,'C<C');

SELECT P1.id,

csv = STUFF

(

SELECT ',' + P2.potype

FROM @mytable P2

WHERE P2.id = P1.id

ORDER BY P2.potype ASC

FOR XML PATH(''), TYPE

).value('./text()[1]', 'VARCHAR(MAX)')

, 1, 1, SPACE(0)

)

FROM @mytable P1

GROUP BY P1.id;

:laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

cfrestrepo SSC Enthusiast Points: 144 More actions · Answer 2

I found a better solution in SqlServer 2008

declare @mytable table

(

id int,

potype varchar(10)

)

declare @Text varchar(max)=''

insert into @mytable values (1,'AA')

insert into @mytable values (1,'BB')

insert into @mytable values (1,'CC')

select @Text += potype +','

from @mytable

select left(@Text,len(@Text)-1)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 3

Nagesh S-432384 (6/3/2010)
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

Thanks for the feedback, Nag. And, understood on the time difference. 🙂

--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 SSC Guru Points: 1003859 More actions · Answer 4

cfrestrepo (6/4/2010)
I found a better solution in SqlServer 2008

Ummm... it depends on what you mean by "better". First, that solution will work on all versions of SQL Server (observing that you need to use 8000 instead of MAX prior to SS 2k5). However, when compared to the XML method used previous on this thread, the concatenation method you used is a form of RBAR and is also a fair bit slower than the XML concatenation method.

So, let me ask... what do you mean by "better" in this case?

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

changbluesky SSC Eights! Points: 807 More actions · Answer 5

Nagesh S-432384 (5/31/2010)

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

.

Dear Nag,

Until now, it also confused me how the sql server can automatically catenate the string together. As far as I know, SQL is ad-hoc query and will execute all at once.

OR. it execute one by one,catenate and assign a variable. SO HOW?

Thanks!:blink: