Autogenerate id - 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

General SQL Server Forums

New to SQL Server Programming

Autogenerate id

Author

Topic

ArpitNarula
Starting Member

16 Posts

Posted - 2011-03-08 : 07:30:25

Hi guys,

Below is the scenerio.

Table A:
system name
A c
A d
A e
B c
B d
B e

need output in another table as

system name Grpid
A c 1
A d 2
A e 3
B c 1
B d 2
B e 3

Can the groupid be auto generated

Thanks in advance.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-08 : 07:37:02

while creating the Grpid column make it identity column.

ArpitNarula
Starting Member

16 Posts

Posted - 2011-03-08 : 07:47:34

quote:
Originally posted by ahmeds08

while creating the Grpid column make it identity column.

I beleive that will give the output as:
system name Grpid
A c 1
A d 2
A e 3
B c 4
B d 5
B e 6

Though I need...

system name Grpid
A c 1
A d 2
A e 3
B c 1
B d 2
B e 3

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-08 : 09:44:29

declare @Table_A table ([system] varchar(255), [name] varchar(255))
insert @Table_A
select 'A', 'c' union all
select 'A', 'd' union all
select 'A', 'e' union all
select 'B', 'c' union all
select 'B', 'd' union all
select 'B', 'e'

select * from @Table_A

select
[system],
[name],
row_number() over (partition by [system] order by [name] asc) as Grp
from @Table_A

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources