Show Record x-Times

[UltraSmooth]

This query will probably not make much sense but is there a way to select a single record from a table but have the record show up x number of times (x specified by parameter at run-time).

This would be the same as doing a union all on multiple copies of the same query, ie.,

SELECT field1 FROM table WHERE field2='value'
UNION ALL
SELECT field1 FROM table WHERE field2='value'
UNION ALL
SELECT field1 FROM table WHERE field2='value'
.....

I was hoping for something simpler.

 

[UltraSmooth]

I've solved the query, not that it's useful in many situations , but just incase anyone is interested.

select * from
(select field1,'ITEM' as dummy_field
from table1) item
join
(select 'ITEM' as dummy_field
from table1
limit X) item_count
on item.dummy_field=item_count.dummy_field;

X is just the number of times I want the record to occur.

 

[r937]

this is another of the many scenarios in which an integers table is supremely useful

CREATE TABLE integers
(i INTEGER NOT NULL PRIMARY KEY);
INSERT INTO integers
VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

now you can generate your data with a cross join

SELECT field1 
  FROM integers
CROSS
  JOIN table 
 WHERE i < 4 -- 4 is the number or repeats you want
   AND field2='value'

r937.com | rudy.ca