Scrubbing cursor rows to colum

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

SQL Server Development (2000)

Scrubbing cursor rows to colum

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2004-02-19 : 16:20:41

I need to scrub some data and pivot some rows to a column.

I am not as familiar with cursors because I really don't use them.

I thought this may be an opportunity to try and recurse on the row.

Below is sample data and sample cursor.

I keep getting: So I know I didn't code it right.

Server: Msg 217, Level 16, State 1, Procedure ps_sierra_parcel_scrub, Line 37
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

CATID o_num parcel
11 WM330489A 59-052-694-601-00
11 WM330489A 0354-121-001-00
11 WM330489A 80-02-420-023-00
11 WM351178 3905-26-284-018
11 WM351178 0628459007
11 WM351178 0616135006
11 WM351180 0609493002
11 WM351180 0609498006
11 WM351181 0632107155
11 WM351181 0632107156
12 xxxx ...etc

CREATE PROC ps_sierra_parcel_scrub
(
@o_num varchar(50),
@catid int
)
AS
SET NOCOUNT ON

DECLARE
@result varchar(8000),
@cur_o_num varchar(50),
@cur_catid int

SELECT @result = ''
SELECT
@result = @result + parcelnum + ', '

FROM LEGAL..tblsierra_parcel
WHERE o_num= @o_num AND catid = @catid

--WHAT I WANT
--UPDATE othertable SET field=
SELECT LEFT(@result, LEN(@result) -2 )

DECLARE scrub_parcel CURSOR LOCAL FOR
SELECT o_num, catid
FROM LEGAL..tbllegal
WHERE o_num > @o_num

OPEN scrub_parcel
FETCH NEXT FROM scrub_parcel INTO @cur_o_num, @cur_catid
WHILE @@FETCH_STATUS=0 BEGIN
EXEC ps_sierra_parcel_scrub @cur_o_num, @cur_catid
FETCH NEXT FROM scrub_parcel INTO @cur_o_num, @cur_catid
END

CLOSE scrub_parcel
DEALLOCATE scrub_parcel

==============================================================================
I know I could use this but, I would like to figure out the cursor.

CREATE TABLE #t (fn int, val varchar(100) , res varchar(7000),
CONSTRAINT thecons PRIMARY KEY (fn,val)
)

INSERT INTO #t(fn,val) VALUES( 1, 'abc')
INSERT INTO #t(fn,val) VALUES( 2, 'nmo')
INSERT INTO #t(fn,val) VALUES( 1, 'def')
INSERT INTO #t(fn,val) VALUES( 1, 'cpq')
INSERT INTO #t(fn,val) VALUES( 2, 'yzz')

SET NOCOUNT ON
DECLARE
@list varchar(8000),
@x int

SELECT
@list = '',
@x = -1

UPDATE #t SET
@list = res = CASE WHEN @x <> fn THEN val
ELSE @list + ', ' + val
END,
@x = fn
SELECT fn, MAX(val) v, MAX(res) r
FROM #t a
GROUP BY fn

DROP TABLE #t

slow down to move faster...

skillile
Posting Yak Master

208 Posts

Posted - 2004-02-19 : 16:44:58

I think I got it...

CREATE PROC ps_sierra_parcel_scrub

AS
SET NOCOUNT ON

DECLARE
@result varchar(8000),
@cur_o_num varchar(50),
@cur_catid int

DECLARE scrub_parcel CURSOR LOCAL FOR
SELECT o_num, catid
FROM LEGAL..tbllegal

OPEN scrub_parcel
FETCH NEXT FROM scrub_parcel INTO @cur_o_num, @cur_catid
WHILE @@FETCH_STATUS = 0 BEGIN

SELECT @result = ''
SELECT
@result = @result + parcelnum + ', '

FROM LEGAL..tblsierra_parcel
WHERE o_num= @cur_o_num AND catid = @cur_catid

--WHAT I WANT
SELECT LEFT(@result, LEN(@result) -2 )

FETCH NEXT FROM scrub_parcel INTO @cur_o_num, @cur_catid
END

CLOSE scrub_parcel
DEALLOCATE scrub_parcel

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources