CODEWITHSUNDEEP
sqlsql-servert-sql
gpr
gpr

Reputation: 35

How to generate a unique number for a combination of itemIDs in SQL Server

I have a table with two columns which should generate a unique number for a combination of itemIDs as below.

Please help in query writing. thanks.

seq_no  ItemId
-------------------
15000   AA-00001152
15000   AA-00001152
15001   AA-00001153
15002   AA-00001154
15002   AA-00001154
15003   AA-00001155
15004   AA-00001156
15004   AA-00001156

EDIT :

My output should come like this in uniqueNumber.. here my uniquenurber should start with 15000 and increment by 1,but every multiple same Itemid, uniquenumber should be same as shown below.

UniqueNumber | seq_no | ItemId      |
+--------------+--------+-------------+
| 15000       | 15000  | AA-00001152 |
+--------------+--------+-------------+
| 15000       | 15000  | AA-00001152 |
+--------------+--------+-------------+
| 15001       | 15001  | AA-00001153 |
+--------------+--------+-------------+
| 15002       | 15002  | AA-00001154 |
+--------------+--------+-------------+
| 15002       | 15002  | AA-00001154 |
+--------------+--------+-------------+
| 15003       | 15003  | AA-00001155 |
+--------------+--------+-------------+
| 15004       | 15004  | AA-00001156 |

Upvotes: 1

Views: 596

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You appears to want dense_rank() function

select *, 
         15000+(DENSE_RANK() over (order by ItemId)-1) UniqueNumber 
from table t

Upvotes: 0

PSK
PSK

Reputation: 17953

If you want to generate a unique number for itemid which are duplicate, you can use ROW_NUMBER() with PARTITION BY ItemId as following.

This will generate a unique number for each repeating ItemId

declare  @table table( seq_no int,   ItemId varchar(100))
insert into @table
values
(15000  , 'AA-00001152'),
(15000  , 'AA-00001152'),
(15001  , 'AA-00001153'),
(15002 ,  'AA-00001154'),
(15002 ,  'AA-00001154'),
(15003 ,  'AA-00001155'),
(15004 ,  'AA-00001156'),
(15004  , 'AA-00001156')

SELECT ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY ItemId) UniqueNumber,*
FROM @table

OUTPUT

+--------------+--------+-------------+
| UniqueNumber | seq_no | ItemId      |
+--------------+--------+-------------+
| 1            | 15000  | AA-00001152 |
+--------------+--------+-------------+
| 2            | 15000  | AA-00001152 |
+--------------+--------+-------------+
| 1            | 15001  | AA-00001153 |
+--------------+--------+-------------+
| 1            | 15002  | AA-00001154 |
+--------------+--------+-------------+
| 2            | 15002  | AA-00001154 |
+--------------+--------+-------------+
| 1            | 15003  | AA-00001155 |
+--------------+--------+-------------+
| 1            | 15004  | AA-00001156 |
+--------------+--------+-------------+
| 2            | 15004  | AA-00001156 |
+--------------+--------+-------------+

If you want to generate unique number across all the items, you can also try like following.

SELECT CAST(CAST(seq_no AS VARCHAR(10)) + CAST(UniqueNumber AS VARCHAR(10)) AS INT) UniqueNumber,seq_no,ItemId
FROM
(
    SELECT  ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY ItemId) UniqueNumber,*
    FROM @table
) T

OUTPUT

+--------------+--------+-------------+
| UniqueNumber | seq_no | ItemId      |
+--------------+--------+-------------+
| 150001       | 15000  | AA-00001152 |
+--------------+--------+-------------+
| 150002       | 15000  | AA-00001152 |
+--------------+--------+-------------+
| 150011       | 15001  | AA-00001153 |
+--------------+--------+-------------+
| 150021       | 15002  | AA-00001154 |
+--------------+--------+-------------+
| 150022       | 15002  | AA-00001154 |
+--------------+--------+-------------+
| 150031       | 15003  | AA-00001155 |
+--------------+--------+-------------+
| 150041       | 15004  | AA-00001156 |
+--------------+--------+-------------+
| 150042       | 15004  | AA-00001156 |
+--------------+--------+-------------+

Upvotes: 1

Related Questions