5

I have a table in MS Access with rows which have a column called "repeat"

I want to SELECT all the rows, duplicated by their "repeat" column value.

For example, if repeat is 4, then I should return 4 rows of the same values. If repeat is 1, then I should return only one row.

This is very similar to this answer:

https://stackoverflow.com/a/6608143

Except I need a solution for MS Access.

Community
  • 1
  • 1
Matthew
  • 10,244
  • 5
  • 49
  • 104

3 Answers3

6

First create a "Numbers" table and fill it with numbers from 1 to 1000 (or up to whatever value the "Repeat" column can have):

CREATE TABLE Numbers
  ( i INT NOT NULL PRIMARY KEY
  ) ;

INSERT INTO Numbers 
  (i)
VALUES
  (1), (2), ..., (1000) ;

then you can use this:

SELECT t.*
FROM TableX AS t
  JOIN
     Numbers AS n
       ON n.i <= t.repeat ;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • +1, Answer will be accepted except that your code is not valid MS Access SQL. You need parenthesis around the the table and the join condition. – Matthew Sep 02 '13 at 06:40
  • Actually, for the join condition in Access you don't need parens. For the `insert into ... values ...` though you do need to insert one value at a time, since Access doesn't support the multi-insert syntax. – Yawar Sep 24 '13 at 05:31
  • If you want a lot of numbers in your numbers table, you can simplify by creating the table with `CREATE TABLE numbers ( i INT IDENTITY)` and then do the inserting with `DECLARE @i int = 0; WHILE @i < 1024 BEGIN SET @i = @i + 1; INSERT INTO numbers DEFAULT VALUES; END;` Obviously you can change the 1024 to whatever number you want. In my case I wanted 1 to 1024 in the table. – Dylan Smith Mar 28 '18 at 19:36
  • @DylanSmith does Access have `IDENTITY`? If I remember well, it calls them differently. – ypercubeᵀᴹ Mar 28 '18 at 20:08
  • Ah, I missed that. I don't think it does. I was thinking SQL Server. Never mind then! – Dylan Smith Mar 28 '18 at 20:30
  • @DylanSmith and it was just an example/pseudocode, to show that the table has to be filled. There are different ways to do this, depending on the DBMS and the number of rows you want to fill. (for a billion rows for example, I wouldn't use the WHILE loop ;) – ypercubeᵀᴹ Mar 28 '18 at 20:37
  • @ypercubeᵀᴹ what would you use for a billion rows? – Dylan Smith Mar 28 '18 at 20:42
  • @DylanSmith probably a cross join of some sort. Take a table of 1000 rows (which you can easily get in SQL Server with a simple `SELECT TOP (1000) rn = ROW_NUMBER() FROM sys.all_objects;` ) and cross join it to itself 4 times, for example. (see the various answers here: https://stackoverflow.com/questions/12183062/how-to-ensure-contiguity-of-a-tally-table ) In Postgres, there's the `generate_series()` function that produces a number table on the fly so it doesn't need a number table at all. – ypercubeᵀᴹ Mar 28 '18 at 21:16
1

If repeat has only small values you can try:

select id, col1 from table where repeat > 0
union all
select id, col1 from table where repeat > 1
union all
select id, col1 from table where repeat > 2
union all
select id, col1 from table where repeat > 3
union all ....
slavoo
  • 5,798
  • 64
  • 37
  • 39
0

What you can do is to retrieve the one 'unique' row and copy this row/column into a string however many copies you need from it using a for loop.

heinkasner
  • 425
  • 5
  • 18