convert cursor to SQL statement

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)

convert cursor to SQL statement

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-01-19 : 10:57:39

How can I convert the below cursor to the simple SQL statement?

truncate table A

declare @column varchar(3), @tsql varchar(200)

declare c1 cursor
for
select name from syscolumns where id = (select id from sysobjects where name = 'A')
and name between '0' and 'A'
open c1
fetch next from c1 into @column
while (@@FETCH_STATUS = 0)
begin

set @tsql = 'insert into A select '''+@column+''' as ''category'',person_id, status, location, SSN, sum(['+@column+']) as [count] from B group by status, location,ssn, ['+@column+'] having sum(['+@column+']) > 0'
exec (@tsql)

fetch next from c1 into @column

end

close c1
deallocate c1

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-19 : 12:58:28

Yeah, hard code the statements....are the number of tables growing on the fly?

Is it your job to maintain the db?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-19 : 13:32:14

try normalizing your database, and then you won't have to select columns dynamically.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources