CODEWITHSUNDEEP
sqlsql-serversql-server-2000
JetJack
JetJack

Reputation: 988

How to create a idendity for each id

Is it possible to create a composite key in sql 2000

code    id
abc      1
abc      2
abc      3
def      1
def      2
ghi      1

where the id restarts the count at each change of code. I need the numbering to be exactly like that either by creating a table or other SELECT statement trickery.

how to do this in sql server 2000

Need Query Help

Upvotes: 0

Views: 71

Answers (2)

anon
anon

Reputation:

Here is one way to retrieve this data at runtime, without having to actually store it in the table, which is incredibly cumbersome to try and maintain. I'm using a #temp table here but you can pretend #a is your permanent table. As is, this will support up to 256 duplicates. If you need more, it can be adjusted.

CREATE TABLE #a(code VARCHAR(32));

INSERT #a SELECT 'abc'
UNION ALL SELECT 'abc'
UNION ALL SELECT 'abc'
UNION ALL SELECT 'def'
UNION ALL SELECT 'def'
UNION ALL SELECT 'ghi';
GO

SELECT x.code, id = y.number FROM 
(
  SELECT code, maxid = COUNT(*) FROM #a GROUP BY code
) AS x
CROSS JOIN 
(
  SELECT DISTINCT number FROM master..spt_values
  WHERE number BETWEEN 1 AND 256
) AS y
WHERE x.maxid >= y.number;

DROP TABLE #a;

Upvotes: 2

Yograj Gupta
Yograj Gupta

Reputation: 9869

You can try this

INSERT INTO TABLENAME (code, id) VALUES( 'code',
(Select ISNULL(MAX(id), 0) FROM TableName where code = 'code')+1)

Upvotes: 0

Related Questions