Postgres duplicate column based on value in column

Question

I've the following table in postgresql

id	product	customer	price	amount
1	Television	Alfred	12.00	1
2	Radio	Richard	20.00	3
3	Mobile	Richard	40.00	5

Is there some way via SQL to duplicate the line n-times in case the amount is greater than one?

This is how the table should look like after running the query:

id	product	customer	price	amount
1	Television	Alfred	12.00	1
2	Radio	Richard	20.00	3
3	Mobile	Richard	40.00	5
4	Radio	Richard	20.00	3
5	Radio	Richard	20.00	3
6	Mobile	Richard	40.00	5
7	Mobile	Richard	40.00	5
8	Mobile	Richard	40.00	5
9	Mobile	Richard	40.00	5

f.e. we now have 3 Radios that do match with the amount 3. Unfortunately, I do not even find a correct Google search term.

FlexYourData · Accepted Answer · 2022-02-08 15:17:44Z

2

You can use generate_series in the group by for this:

DROP TABLE IF EXISTS _dat;

CREATE TEMP TABLE _dat 
(
  id         INT,
  product    TEXT,
  customer   TEXT,
  price      DECIMAL,
  amount     INT
);


INSERT INTO _dat (id,product,customer,price,amount) 
VALUES 
(1,'Television','Alfred',12,1),
(2,'Radio','Richard',20,3),
(3,'Mobile','Richard',40,5);

SELECT id,
       product,
       customer,
       price,
       amount
FROM _dat
GROUP BY id,
         product,
         customer,
         price,
         amount,
         GENERATE_SERIES(1,amount);

answered Feb 8, 2022 at 15:17

FlexYourData

2,4941 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2022-02-08 15:08:16Z

1

You can do a cross join using generate_series()

select row_number() over () as id,  
       t.product, t.customer, t.price, 
       t.amount
from the_table t
   cross join generate_series(1, t.amount) as x

answered Feb 8, 2022 at 15:08

user330315

Collectives™ on Stack Overflow

Postgres duplicate column based on value in column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related