CODEWITHSUNDEEP
sqlpostgresql
Gothmog
Gothmog

Reputation: 891

How to multiply a row?

I have a Postgres 9.0 query returning results in a way similar to this:

item;qty
AAAA;2
EEEE;3

What I would like is to transform that into:

AAAA
AAAA
EEEE
EEEE
EEEE

Is there any way I can do that on simple, i.e., without stored procedures, functions, etc?

Upvotes: 0

Views: 225

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657892

Consider the following demo:

CREATE TEMP TABLE x(item text, qty int);

INSERT INTO x VALUES
 ('AAAA',2)
,('EEEE',3)
,('IIII',4);

SELECT regexp_split_to_table(rtrim(repeat(item||'~#~',qty),'~#~'),'~#~') AS item
FROM   x;

Produces exactly the requested result.

  • In my tests it performs faster by an order of magnitude than the solution with generate_series().
  • Additional bonus: works with any number of qty.
  • Weakness: you need a delimiter-string not contained in any item.

Upvotes: 2

Nordic Mainframe
Nordic Mainframe

Reputation: 28757

There's a function 'generate_series' which can be used to generate a table of values. These can be used to repeat a column via joining:

select item 
from data,generate_series(0,1000) 
where generate_series<qty order by item;

Upvotes: 2

MatBailie
MatBailie

Reputation: 86765

SELECT
  myTable.item
FROM
  myTable
INNER JOIN
  (SELECT 1 AS counter UNION ALL SELECT 2 UNION ALL SELECT 3) AS multiplier
    ON multiplier.counter <= myTable.qty

Increase the number of UNIONS based on your Maximum value in qty


But I'd also follow @djacobson's advice : explain why you want to do this, as the may be a completely different approach altogether. Doing this feels, ummm, odd...

Upvotes: 0

Related Questions