should I use cursor?Crossposted to newbies forum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

should I use cursor?Crossposted to newbies forum

Author

Topic

TomKa_t
Starting Member

4 Posts

Posted - 2005-12-28 : 11:35:47

Hi,
It's the second day I'm trying to solve this problem.
I have a selection from four tables, two of columns looks like:


Dovgan      Rice
Dovgan      Cereals
Nike        Boots
Nike        Jacket
Nike        Cap

I wanna make one more selection to make it look like


Dovgan      Rice, Cereals
Nike        Boots, Jacket, Cap

It's very easy to make through function if I had rights to create table for this. But I cannot that is why I created temporary table #Zapros in Stored Procedure, fill it and trying to get only rows that looks like as I said.

Stored Procedure looks like:


DECLARE @Firm VARCHAR(255), @FirmProduction VARCHAR(1024)
DECLARE @NextFirm VARCHAR(255), @NextFirmProduction VARCHAR(255)
DECLARE cursorFirm CURSOR
FOR
SELECT Firm, FirmProduction
        FROM #Zapros -- this is my temporary table
	ORDER BY Firm
OPEN cursorAgency
FETCH NEXT FROM cursorFirm into @Firm, @FirmProduction 
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM cursorFirm into @NextFirm, @NextFirmProduction
 IF @Firm= @NextFirm
 BEGIN

 update   #Zapros 
 set #Zapros.FirmProduction = #Zapros.FirmProduction +'|' + @NextFirmProduction
 END
 SET @Firm = @NextFirm
END


CLOSE cursorFirm
DEALLOCATE cursorFirm

but something's going wrong and I cannot find where is a mistake. Besides this is my first Cursor. Probably someone can help me with this, or at least suggest me some direction to search in.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-28 : 11:58:12

U can write a function to return the output as
Rice, Cereals
Boots, Jacket, Cap

U can make use of that function with a query to get the desired output.
Eg . Select Col_1, ConcatFunc() from MyTbl
-- U have to write the ConcatFunc() similar to following
Select @s = @s + Col_2 + ',' from MyTbl .....

U don't have to create tables in that case.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 01:15:40

Refer this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=59609

Madhivanan

Failing to plan is Planning to fail

TomKa_t
Starting Member

4 Posts

Posted - 2005-12-29 : 07:54:14

Thanks to EVERYBODY who tried to help!!! And special thanx to madhivanan from this forum and to Lenok and her husband from my ICQ ContactList.
I adapt the function from sqljunkies for my needs + Lenok_n_her_Husband helped me to create right SELECT'ion.
THANX, Guys!!!
Now I need to add some input parameters to my function and it is ready for use on my asp page.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 07:58:22

>>And special thanx to madhivanan from this forum

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources