Alphanumeric sequence in SQL Server-CodePudding

For a single id column, we have sequence number 01, 02, 03 upto 99 repeating twice /thrice.

Example:

ID SEQ_NO
----------
2   01
2   02
2   03
.
.
.
2   99
2   01 
2   02
2   99

We have a requirement to add AA prefix to second time when it is looping on seq_no, and for third time it should be BB.

Can anyone explain how to do this?

CodePudding user response：

Try the following using the ROW_NUMBER function:

If you want only to select SEQ_NO as a new column:

WITH CTE AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, SEQ_NO ORDER BY SEQ_NO) rn 
  FROM table_name
)
SELECT ID, SEQ_NO,
       CASE 
         WHEN rn>1 THEN
          CONCAT(CHAR(rn 63), CHAR(rn 63), SEQ_NO) 
         ELSE SEQ_NO
       END AS new_seq
FROM CTE 
WHERE rn <= 27
ORDER BY ID, new_seq

If you want to update the SEQ_NO column:

WITH CTE AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, SEQ_NO ORDER BY SEQ_NO) rn 
  FROM table_name
)
UPDATE CTE SET SEQ_NO = CONCAT(CHAR(rn 63), CHAR(rn 63), SEQ_NO) 
        
WHERE rn > 1 AND rn <= 27

See a demo with a set of data where seq (01 - 10) is repeated three times.