I have a table.
ID name col lev
1 "Rack 1" 9 7
2 "Rack 2" 6 7
3 "Rack 3" 6 7
4 "Rack 4" 7 7
I want to crate a second table from this one that uses 'col' and 'lev' number as to range. Also, 'lev' needs to be in letters.
The result should be a new tble that looks like this.
ID Name col lev
1 Rack 1 1 A
2 Rack 1 1 B
3 Rack 1 1 C
4 Rack 1 1 D
5 Rack 1 1 E
6 Rack 1 1 F
7 Rack 1 1 G
8 Rack 1 2 A
9 Rack 1 2 B
10 Rack 1 2 C
11 Rack 1 2 D
12 Rack 1 2 E
13 Rack 1 2 F
14 Rack 1 2 G
15 Rack 1 3 A
16 Rack 1 3 B
17 Rack 1 3 C
18 Rack 1 3 D
19 Rack 1 3 E
20 Rack 1 3 F
21 Rack 1 3 G
Any ideas?
You can use generate_series to achieve what you're looking for. Also, create another table for converting your lev integers to text values.
create table my_table (
id integer,
name varchar(10),
col integer,
lev integer
);
insert into my_table values
(1, 'Rack 1', 9, 7),
(2, 'Rack 2', 2, 3);
create table lev_to_text (
lev_val integer,
text_val varchar(1)
);
insert into lev_to_text values
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E'),
(6, 'F'),
(7, 'G'),
(8, 'H');
with lev_series as (
select name,
generate_series(1, lev) as lev
from my_table
)
select m.name,
generate_series(1, m.col) as col,
ltt.text_val as lev
from my_table m
join lev_series ls
on m.name = ls.name
join lev_to_text ltt
on ls.lev = ltt.lev_val
order by 1,2,3;
| name | col | lev |
|---|---|---|
| Rack 1 | 1 | A |
| Rack 1 | 1 | B |
| Rack 1 | 1 | C |
| Rack 1 | 1 | D |
| Rack 1 | 1 | E |
| Rack 1 | 1 | F |
| Rack 1 | 1 | G |
| Rack 1 | 2 | A |
| Rack 1 | 2 | B |
| ... | ... | ... |
| Rack 1 | 9 | F |
| Rack 1 | 9 | G |
| Rack 2 | 1 | A |
| Rack 2 | 1 | B |
| Rack 2 | 1 | C |
| Rack 2 | 2 | A |
| Rack 2 | 2 | B |
| Rack 2 | 2 | C |
If you need an arbitrary ID column, then just add a row_number() to your select statement.