In SQL Server 2016 Standard, how to create a column with a unique value based on the values of 2 other columns?
Please see below example to illustrate the question: I would like to create column 4 whereby an ascending numeric value is given to each matching fabric_code and item_number pair.
I.e. fabric_code = '29989260' and item_number = '49002.01' get the Unique_ID = '1'
| fabric_code | item_number | doc_num | Unique_ID? |
|---|---|---|---|
| 29989260 | 49002.01 | 1 | 1 |
| 29989260 | 49002.01 | 2 | 1 |
| 29989261 | 49002.02 | 1 | 2 |
| 29989261 | 49002.02 | 2 | 2 |
| 29989261 | 49002.02 | 3 | 2 |
| 29989262 | 49002.03 | 1 | 3 |
Ideally, I don't want to add a new column to the database, output using a SELECT statement would be what I'm after.
I think you are looking for something like
SELECT fabric_code, item_number, doc_number,
DENSE_RANK() OVER (ORDER BY fabric_code, item_number) Unique_ID
FROM YourTableName
You can read about dense_rank here. The idea is that the lowest fabric_code/item_number pair (all of them) should be 1, then the next lowest (all of them) will be 2, and so forth. You could change that numbering by adjusting the order by bit. Since it doesn't use partition by it won't reset the numbering for each group.