Teradata SQL - Adding columns of repeating values to existing table

Question

I have a dataset with 5000 records. I want to add 3 columns to this dataset: 1st one with values from 1 to 5, 2nd with values from 1 to 4, 3rd with values from 1 to 3 such as follows -

record_id   ser_num5    ser_num4    ser_num3
1           1           1           1
2           2           2           2
3           3           3           3
4           4           4           1
5           5           1           2
6           1           2           3
7           2           3           1
8           3           4           2
9           4           1           3
10          5           2           1
11          1           3           2
12          2           4           3
13          3           1           1
14          4           2           2
15          5           3           3
16          1           4           1
17          2           1           2
18          3           2           3
19          4           3           1
20          5           4           2
….          
5000

How can I do this in Teradata SQL? I've tried the following code but it didn't work -

CREATE SEQUENCE Test.SerNum5
    AS Int 
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 5
    CYCLE
;

SELECT NEXT VALUE FOR Test.SerNum5;

Also, is there anyway I can somehow merge this piece into an existing "select columns" statement if i can't make the create sequence to work. Thanks for any help!

score 2 · Answer 1 · answered Jul 29 '14 at 02:11

The easiest way is with arithmetic on the id:

select id,
       ((id - 1) mod 5) + 1 as sernum_5,
       ((id - 1) mod 4) + 1 as sernum_4,
       ((id - 1) mod 3) + 1 as sernum_3

If the id is no really contiguous, you can use row_number() instead:

select id,
       ((row_number() over (order by id) - 1) mod 5) + 1 as sernum_5,
       ((row_number() over (order by id) - 1) mod 4) + 1 as sernum_4,
       ((row_number() over (order by id) - 1) mod 3) + 1 as sernum_3

Teradata SQL - Adding columns of repeating values to existing table

1 Answers1