How to rename items by assigning sequential number?

user_1646240 0
Viewed 43

I have the following table:

DECLARE @OperatorPrice TABLE 
(
    ID INT NOT NULL, DealerId INT NULL, DealerName VARCHAR(50) NULL
)

and sample data:

INSERT INTO @OperatorPrice 
(
    ID, DealerId, DealerName
)
VALUES
  (226, 1,  'WestCarDealer')
, (112, 1,  'WestCarDealer')
, (266, 2, 'AO')
, (112, 2, 'AO')
, (93,  3, 'Best on the West')
, (93,  3, 'Best on the West')

What I want is to rename all dealers to 'Dealer1', 'Dealer2', 'Dealer3'. The number should be assigned in ascending order: AO should be renamed to 'Dealer1' cause AO starts with A, Best on the West should be renamed to Dealer2 cause it starts with B, WestCarDealer should be renamed to Dealer3 cause it starts with W.

So the desired output should looks like this:

  (226, 1, 'Dealer3')
, (112, 1, 'Dealer3')
, (266, 2, 'Dealer1')
, (112, 2, 'Dealer1')
, (93,  3, 'Dealer2')
, (93,  3, 'Dealer2')

Approximate quantity of unique DealerName's is 50 dealers.

How can I rename car dealers in that way?

I've tried to use cursors for this purpose, but could not store the number to increment.

pivot tsql sql-server sql
1 Answers

You can use an CTE to update the table variable

Example

;with cte as (
Select *
      ,NewVal = concat('Dealer',dense_rank() over ( order by DealerName))
 From  @OperatorPrice
)
Update cte Set DealerName = NewVal

Updated @OperatorPrice

ID  DealerId    DealerName
226 1           Dealer3
112 1           Dealer3
266 2           Dealer1
112 2           Dealer1
93  3           Dealer2
93  3           Dealer2
user_15700000
Related