Derive additional columns based on existing column

Question

I have 500 records with 10 columns. Based on the Date column I want to create two additional columns (date_id, record_id). The date column is unique.

• Date_id column value should be: F + date + unique identifier.

• Record_id column value should be: C + date + unique identifier.

For example:

Date     
15Jul2017        

Expected output:

Date             date_id                     record_id 
---------------  --------------------------  --------------
15Jul2017        F+15Jul2017+1               C+15Jul2017+1
15Jul2017        F+15Jul2017+2               C+15Jul2017+2
...so on until 500 records
15Jul2017        F+15Jul2017+500             C+15Jul2017+500

Answer 1

You can use row_number():

select t.date,
       ('F+' || date || '+' || row_number() over (order by date)) as date_id,
       ('c+' || date || '+' || row_number() over (order by date)) as record_id
from t;

If your date is really stored as a date and not a string, then you may need to format it to your preferred format.