2

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?

Evg
  • 25,259
  • 5
  • 41
  • 83
abirami ramachandran
  • 37
  • 6
  • 2
    I would suggest not doing that, consider the rules for data normalisation and keep your values discreet in separate columns. I would also not store a sequence *number* as (presumably) a *varchar* and instead prefix a leading zero in the application if required. – Stu Oct 27 '22 at 09:12
  • So the 27th time around, you use `ZZ` as your prefix - and then what for the 28th time?? Seems a very odd requirement / prefixing strategy..... – marc_s Oct 27 '22 at 09:16
  • @Stu .. I do agree but we want to make changes in existing system – abirami ramachandran Oct 27 '22 at 09:19
  • @marc_s For now we need only upto 27th time – abirami ramachandran Oct 27 '22 at 09:20

1 Answers1

2

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.

ahmed
  • 9,071
  • 3
  • 9
  • 22