I've the following table in postgresql

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:

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.

CodePudding user response：

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);

CodePudding user response：

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