CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Gopal
Gopal

Reputation: 11992

How to Create Identity field based on the row

Using SQL Server 2005

Table1

Code ID (identity field)

001 1
001 2
002 1
003 1
003 2

How to create a identity field based on the code.

Need Query Help

Upvotes: 0

Views: 84

Answers (5)

Taryn
Taryn

Reputation: 247870

It looks like you want to implement row_number() which will increment the id value based on the number of code values that you have:

select code, id
from
(
  select code, 
    row_number() over(partition by code order by code) id
  from yourtable
) d;

Using row_number() will allow you to calculate the value when you query the data in your table. See SQL Fiddle with Demo.

If you want to update your table with this value, then you could use something like the following:

;with cte as
(
  select code, id, 
    row_number() over(partition by code order by code) rn
  from yourtable
) 
update cte
set id = rn;

See Demo.

Storing this value in your table will be difficult to maintain if you continue to add new rows for each code, it might be easier to implement the row_number() when you query the data.

Upvotes: 2

Andomar
Andomar

Reputation: 238296

(On rereading your question, I see that your id column is not unique, so it could not be an identity column.)

To create an identity field that uses initial values from the Code column, you could:

-- Create an empty copy of your table
select  top 0 *
into    CopiedTable
from    YourTable;

-- Add an identity column
alter table CopiedTable add id int identity;

-- Copy the rows over, while initializing the identity column from Code
set identity_insert dbo.CopiedTable on

insert  dbo.CopiedTable
        (id, Code)
select  Code
,       Code
from    dbo.YourTable;

set identity_insert dbo.CopiedTable off

-- Drop the old table
drop table dbo.YourTable

-- Rename the copied table
exec sp_rename 'CopiedTable', 'YourTable'

Upvotes: 0

bvr
bvr

Reputation: 4826

Use ROW_NUMBER

SELECT
  code, 
  ROW_NUMBER() OVER(PARTITION BY code ORDER BY code) AS ID
FROM Table1

Upvotes: 0

vikas
vikas

Reputation: 2830

get it from count

DECLARE @ID int = (
    SELECT COUNT(*) + 1 from test_1 WHERE [Code] = @CODE )

Upvotes: -1

marc_s
marc_s

Reputation: 755411

Just like this:

ALTER TABLE dbo.YourTable
ADD NewColumn INT IDENTITY(1,1)

You can define the seed (starting value) as the first parameter, and the step (increments) as the second - so pick whatever makes sense to you; both seed=1 and step=1 seems to be the mostly used defaults.

The column will be added and populated with values when it's creatde.

Upvotes: 2

Related Questions