Skip to content
Advertisement

Repeat Rows certain amount of times for columns

I have this table:

select * from db_table

car            city                  month_number
Honda          Boston                    1
Honda          Boston                    2
Honda          Boston                    3
Honda          Boston                    4
Honda          Boston                    5    <---data ends for Honda and Boston
Toyota         Chicago                   1
Toyota         Chicago                   2
Toyota         Chicago                   3
Toyota         Chicago                   4
Toyota         Chicago                   5
Toyota         Chicago                   6   <---data ends for Toyota and Chicago

I want to be able to self-generate data for each car and city for 24 months cycle, more like auto-increment.

What I am looking for:

car            city                  month_number
Honda          Boston                    1
Honda          Boston                    2
Honda          Boston                    3
Honda          Boston                    4
Honda          Boston                    5
Honda          Boston                    6
Honda          Boston                    7
Honda          Boston                    8
Honda          Boston                    9
Honda          Boston                    10
Honda          Boston                    11
Honda          Boston                    12
Honda          Boston                    13
Honda          Boston                    14
Honda          Boston                    15
Honda          Boston                    16
Honda          Boston                    17
Honda          Boston                    18
Honda          Boston                    19
Honda          Boston                    20
Honda          Boston                    21
Honda          Boston                    22
Honda          Boston                    23
Honda          Boston                    24
Toyota         Chicago                   1
Toyota         Chicago                   2
Toyota         Chicago                   3
Toyota         Chicago                   4
Toyota         Chicago                   5
Toyota         Chicago                   6
Toyota         Chicago                   7
Toyota         Chicago                   8
Toyota         Chicago                   9
Toyota         Chicago                   10
Toyota         Chicago                   11
Toyota         Chicago                   12
Toyota         Chicago                   13
Toyota         Chicago                   14
Toyota         Chicago                   15
Toyota         Chicago                   16
Toyota         Chicago                   17
Toyota         Chicago                   18
Toyota         Chicago                   19
Toyota         Chicago                   20
Toyota         Chicago                   21
Toyota         Chicago                   22
Toyota         Chicago                   23
Toyota         Chicago                   24

How can I do this in postgreSQL?

Advertisement

Answer

You can try to use generate_series with CROSS JOIN a subquery.

Query 1:

SELECT t1.car,
       t1.city,
       month_number
FROM generate_series(1, 24) month_number CROSS JOIN (
   select DISTINCT car,
          city  
  from db_table
) t1
ORDER BY t1.car,t1.city 

Results:

|    car |    city | month_number |
|--------|---------|--------------|
|  Honda |  Boston |            1 |
|  Honda |  Boston |            2 |
|  Honda |  Boston |            3 |
|  Honda |  Boston |            4 |
|  Honda |  Boston |            5 |
|  Honda |  Boston |            6 |
|  Honda |  Boston |            7 |
|  Honda |  Boston |            8 |
|  Honda |  Boston |            9 |
|  Honda |  Boston |           10 |
|  Honda |  Boston |           11 |
|  Honda |  Boston |           12 |
|  Honda |  Boston |           13 |
|  Honda |  Boston |           14 |
|  Honda |  Boston |           15 |
|  Honda |  Boston |           16 |
|  Honda |  Boston |           17 |
|  Honda |  Boston |           18 |
|  Honda |  Boston |           19 |
|  Honda |  Boston |           20 |
|  Honda |  Boston |           21 |
|  Honda |  Boston |           22 |
|  Honda |  Boston |           23 |
|  Honda |  Boston |           24 |
| Toyota | Chicago |            1 |
| Toyota | Chicago |            2 |
| Toyota | Chicago |            3 |
| Toyota | Chicago |            4 |
| Toyota | Chicago |            5 |
| Toyota | Chicago |            6 |
| Toyota | Chicago |            7 |
| Toyota | Chicago |            8 |
| Toyota | Chicago |            9 |
| Toyota | Chicago |           10 |
| Toyota | Chicago |           11 |
| Toyota | Chicago |           12 |
| Toyota | Chicago |           13 |
| Toyota | Chicago |           14 |
| Toyota | Chicago |           15 |
| Toyota | Chicago |           16 |
| Toyota | Chicago |           17 |
| Toyota | Chicago |           18 |
| Toyota | Chicago |           19 |
| Toyota | Chicago |           20 |
| Toyota | Chicago |           21 |
| Toyota | Chicago |           22 |
| Toyota | Chicago |           23 |
| Toyota | Chicago |           24 |
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement