to avoid cursors - SQL Server Forums

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)

to avoid cursors

Author

Topic

marat
Yak Posting Veteran

85 Posts

Posted - 2005-09-12 : 03:13:28

Hi,
I have a table A:
id Letter
1 a
2 b
3 b
4 c
5 b
6 b
7 b
8 f
9 e
10 e
I need to generate groups to get table B
id Letter Group
1 a 1
2 b 2
3 b 2
4 c 3
5 b 4
6 b 4
7 b 4
8 f 5
9 e 6
10 e 6
How I can get this result without using cursor?
Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-12 : 05:59:04

this should help:
http://www.sqlteam.com/item.asp?ItemID=12654

Go with the flow & have fun! Else fight the flow

marat
Yak Posting Veteran

85 Posts

Posted - 2005-09-12 : 20:47:58

Thanks
I am going to look at it.
Results - later

marat
Yak Posting Veteran

85 Posts

Posted - 2005-09-13 : 07:27:12

Hi Spirit1,
After a few changes to the script you reffered, I got the following:
create table SQLisFun(
urn int Not Null,
letter char(1) Not Null)
GO
insert SQLisFun
select 1, 'a'
union all
select 2, 'b'
union all
select 3, 'b'
union all
select 4, 'c'
union all
select 5, 'b'
union all
select 6, 'b'
union all
select 7, 'b'
union all
select 8, 'f'
union all
select 9, 'e'
union all
select 10, 'e'
union all
select 11, 'e'
union all
select 12, 'a'
union all
select 13, 'a'
union all
select 14, 'c'
union all
select 15, 'c'

select * from SQLisFun

select letter, grp, identity(int,1,1) as MasterGrp
into SQLisFun_Groups
from
(select urn, letter,
(select isnull(max(urn),0)
from SQLisFun G
where G.letter <> GC.letter and G.urn < GC.urn) as grp
from SQLisFun GC) D
group by letter, grp
order by grp

select G.urn,G.letter, MasterGrp
from
(select urn, letter,
(select isnull(max(urn),0)
from SQLisFun G
where G.letter <> GC.letter and G.urn < GC.urn) as grp
from SQLisFun GC) G
inner join SQLisFun_Groups GG
on G.letter = GG.letter and G.grp = GG.grp

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources