Row Count

• joe joe-152023

  SSCommitted

  Points: 1819

  More actions

  February 23, 2009 at 1:32 am

  #128008

  Hi There,

  I have a tabel with data like this,

  AreaID Area_SubAreaID SubAreaID

  5689 568901 01

  5689 568903 03

  5689 568904 04

  5689 568916 16

  2689 268904 04

  2689 268909 09

  2689 268912 12

  2689 268999 99

  But I need a sequential SubAreaID like this "New_SubAreaID",

  AreaID Area_SubAreaID SubAreaID New_SubAreaID

  5689 568901 01 01

  5689 568903 03 02

  5689 568904 04 03

  5689 568916 16 04

  2689 268904 04 01

  2689 268909 09 02

  2689 268912 12 03

  2689 268999 99 04

  Any help is appreciated

  Regards Joejoe

• Hari.Sharma

  SSCertifiable

  Points: 6904

  More actions

  February 23, 2009 at 1:51 am

  #948465

  Hi Joe,

  Use below code and enjoy!!!

  CREATE TABLE #T (AreaID int, Area_SubAreaID int, SubAreaID smallint)

  GO

  INSERT INTO #T

  SELECT5689, 568901, 01 UNION

  SELECT5689, 568903, 03 UNION

  SELECT5689, 568904, 04 UNION

  SELECT5689, 568916, 16 UNION

  SELECT2689, 268904, 04 UNION

  SELECT2689, 268909, 09 UNION

  SELECT2689, 268912, 12 UNION

  SELECT2689, 268999, 99

  GO

  SELECT * FROM #T Order By AreaID Desc

  SELECT *,Row_Number() OVER (Partition By AreaID ORDER BY Area_SubAreaID) New_SubAreaID

  FROM #T Order By AreaID Desc

  GO

  Cheers,
  Hari
  Tips & Tricks for SQL BI Developers

• Alexander Kovacs

  Hall of Fame

  Points: 3075

  More actions

  February 23, 2009 at 1:53 am

  #948467

  do you look for sometinh like this:

  declare @area table(

  AreaID int,

  Area_SubAreaID int,

  SubAreaID varchar(2)

  )

  insert into @area

  (AreaID, Area_SubAreaID , SubAreaID )

  select 5689 , 568901 , '01'

  union all

  select 5689 , 568903 , '03'

  union all

  select 5689 , 568904 , '04'

  union all

  select 5689 , 568916 , '16'

  union all

  select 2689 , 268904 , '04'

  union all

  select 2689 , 268909 , '09'

  union all

  select 2689 , 268912 , '12'

  union all

  select 2689 , 268999 , '99'

  select

  AreaID,

  Area_SubAreaID,

  SubAreaID ,

  right( '00' +convert(varchar,row_number() over( partition by AreaID order by Area_SubAreaID)),2) as New_SubAreaID

  from

  @area

  result:

  AreaID Area_SubAreaID SubAreaID New_SubAreaID

  26892689040401

  26892689090902

  26892689121203

  26892689999904

  56895689010101

  56895689030302

  56895689040403

  56895689161604