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?
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.