Question 348630 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to generate multiple rows with the key column from another table

I need to generate multiple rows for each record in table into another table. This is to generate an excel file for user to key in some data and later import into database.

State Branch
State1 Branch1
State1 Branch2
State1 Branch3
State2 Branch1
State3 Branch1
State3 Branch2
State4 Branch1
State4 Branch2
State4 Branch3
State4 Branch4

Table above will become like this

State Branch
State1 Branch1
State1 Branch2
State1 Branch3
State1 Branch4
State1 Branch5
State2 Branch1
State2 Branch2
State2 Branch3
State2 Branch4
State2 Branch5
State3 Branch1
State3 Branch2
State3 Branch3
State3 Branch4
State3 Branch5
State4 Branch1
State4 Branch2
State4 Branch3
State4 Branch4
State4 Branch5

Can see that, State1-Branch1 only have 3 records, but I need to generate 5 rows, and same for other branches.
This will then export to excel file and let user to key in the information.
And the end the file will import back to db.

May I know how to generate the table with 5 lines for each branch?

Tags:

2

Answers


  1. 0

    Here is a way to do it using GENERATE_SERIES() and CROSS JOIN :

    SELECT t.State, 'Branch' || s.Branch AS Branch
FROM generate_series(1, 5) AS s(Branch)
CROSS JOIN (
  SELECT DISTINCT State FROM mytable
) as t
ORDER BY t.State

    Demo here

    Login or Signup to reply.
  2. 0 
    select 'State'||state, 'Branch'||generate_series(1,5)
from generate_series(1,4)as a(state);
    ?column? ?column?
    State1 Branch1
    State1 Branch2
    State1 Branch3
    State1 Branch4
    State1 Branch5
    State2 Branch1
    State2 Branch2
    State2 Branch3
    State2 Branch4
    State2 Branch5
    State3 Branch1
    State3 Branch2
    State3 Branch3
    State3 Branch4
    State3 Branch5
    State4 Branch1
    State4 Branch2
    State4 Branch3
    State4 Branch4
    State4 Branch5

    If you only need to generate the state labels and branches are listed somewhere:

    select 'State'||state, branch
from generate_series(1,4)as a(state),
     (select distinct branch from branches)b;

    If you already have tables listing all states as well as all branches you want:

    select state, branch
from (select distinct state  from states)s,
     (select distinct branch from branches)b;

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search