SQL: Repeat a result row multiple times, and number the rows

Question

I have a SQL query with a result like this:

value | count
------+------
foo   |     1
bar   |     3
baz   |     2

Now I want to expand this so that each row with a count larger than 1 occurs multiple times. I also need these rows to be numbered. So I would get:

value | count | index
------+-------+------
foo   |     1 |     1
bar   |     3 |     1
bar   |     3 |     2
bar   |     3 |     3
baz   |     2 |     1
baz   |     2 |     2

I have to make this work on all the major databases (Oracle, SQL Server, MySQL, PostgreSQL, and maybe more). So a solution that works across different databases would be ideal, but clever ways to make it work on any database are appreciated.

score 33 · Answer 1 · answered May 03 '12 at 01:01

33

You could use a numbers table

SELECT value, count, number
FROM table
    JOIN Numbers 
        ON table.count >= Numbers.number

Here is a SQLFiddle using MSSQL

answered May 03 '12 at 01:01

Justin Pihony

66,056
18
147
180

Any insight into why this works? Worked great for my solution 4 years later btw. – nzaleski Nov 17 '16 at 19:03
The simplest explanation is that instead of joining on 1 row, it joins on all the rows up until the number after the count. ie. Count = 3, then it matches 1,2,3 creating a 3|1, 3|2, and 3|3 result – Justin Pihony Nov 17 '16 at 20:11
what is numbrs table?? is it system table? – Bhanu Tez Jun 15 '21 at 15:34

score 25 · Accepted Answer · edited May 23 '17 at 12:26

For MySQL, use the poor man's generate_series, which is done via views. MySQL is the only RDBMS among big four that don't has any CTE feature.

Actually you can use this technique on database that supports view. So that's virtually all database

Generator technique sourced here: http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_code

The only minor modification we made is we replace the bitwise (shift left and bitwise or) technique from the original technique with mere multiplication and addition respectively; as Sql Server and Oracle has no shift left operator.

This abstraction is 99% guaranteed to work on all database, except Oracle; Oracle's SELECT can't function without any table, in order to do this, one need to select from dummy table, Oracle provided one already, it's called DUAL table. Database portability is a pipe dream :-)

Here's the abstracted views that works on all RDBMS, devoid of bitwise operations(which is not really a necessity anyway in this scenario) and feature nuances(we remove OR REPLACE on CREATE VIEW, only Postgresql and MySQL supports them) among all major database.

Oracle caveat: Just put FROM DUAL after each SELECT expression

CREATE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE VIEW generator_256
AS SELECT ( ( hi.n * 16 ) + lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE VIEW generator_4k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE VIEW generator_64k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE VIEW generator_1m
AS SELECT ( ( hi.n * 65536 ) + lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

Then use this query:

SELECT t.value, t.cnt, i.n
FROM tbl t
JOIN generator_64k i 
ON i.n between 1 and t.cnt
order by t.value, i.n

Postgresql: http://www.sqlfiddle.com/#!1/1541d/1

Oracle: http://www.sqlfiddle.com/#!4/26c05/1

Sql Server: http://www.sqlfiddle.com/#!6/84bee/1

MySQL: http://www.sqlfiddle.com/#!2/78f5b/1

This answer helped me solve a major issue in our queries and helped me in constructing an entire new range of reports using this code. Wish I could do more than just up voting this answer. :) — Neels, Mar 18 '14 at 11:11

Michael Buen · Answer 3 · 2012-05-04T13:34:09.163

7

MySQL is really the IE of the database world, it's such a holdout when it comes to standards and features.

Works on all major RDBMS except MySQL:

with 
-- Please add this on Postgresql:
-- RECURSIVE
tbl_populate(value, cnt, ndx) as
(
  select value, cnt, 1 from tbl

  union all

  select t.value, t.cnt, tp.ndx + 1
  from tbl t
  join tbl_populate tp 
  on tp.value = t.value  
  and tp.ndx + 1 <= t.cnt
)
select * from tbl_populate
order by cnt, ndx

SQL Server : http://www.sqlfiddle.com/#!6/911a9/1

Oracle : http://www.sqlfiddle.com/#!4/198cd/1

Postgresql: http://www.sqlfiddle.com/#!1/0b03d/1

edited May 04 '12 at 13:34

answered May 03 '12 at 02:18

Michael Buen

38,643
9
94
118

Nice, it works, although I don't quite understand what it's doing :-) – cygri May 03 '12 at 09:23
Heheh really? Now I'm inspired to do a blog about recursion on CTE. But basically, row generators are the easiest kind of CTE recursion to make, it follows the logic of tail recursion; tail recursion almost have one-to-one correspondence with loop. Hierarchical recursions and object graph recursions on CTE are the ones a bit harder to understand – Michael Buen May 03 '12 at 09:41
shouldn't there be a `recursive` keyword after the `with` ? – vyegorov May 04 '12 at 12:27
@vyegorov true, for Postgresql only :-) In fact that keyword is included on my sqlfiddle, check the last link(Postgresql) of this answer. Other databases can infer recursion and has no need for that `recursive` directive. database portability is really a pipe dream :-) – Michael Buen May 04 '12 at 13:33

Erwin Brandstetter · Answer 4 · 2012-05-03T22:06:01.810

7

You asked for a db-agnostic solution and @Justin gave you a nice one.
You also asked for

clever ways to make it work on any database

There is one for PostgreSQL: generate_series() does what you asked for out of the box:

SELECT val, ct, generate_series(1, ct) AS index
FROM   tbl;

BTW, I'd rather not use value and count as column names. It's bad practice to use reserved words as identifiers. Using val and ct instead.

edited May 03 '12 at 22:06

answered May 03 '12 at 08:07

Erwin Brandstetter

605,456
145
1,078
1,228

Brilliant! I wish this worked everywhere. And yeah you're right about the reserved words, thanks for pointing out. – cygri May 03 '12 at 09:24

score 4 · Answer 5 · answered May 03 '12 at 01:06

Create a numbers table - its definition may vary slightly depending on platform (this is for SQL Server):

CREATE TABLE Numbers(Number INT PRIMARY KEY);

INSERT Numbers 
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_columns;

Now this temp is also SQL Server, but demonstrates the join syntax that should be valid across the RDBMSes you specify (though I will confess I don't use them so I can't test):

DECLARE @foo TABLE(value VARCHAR(32), [count] INT);

INSERT @foo SELECT 'foo', 1
UNION ALL SELECT 'bar', 3
UNION ALL SELECT 'baz', 2;

SELECT f.value, f.[count], [index] = n.Number
FROM @foo AS f, Numbers AS n
WHERE n.Number <= f.[count];

Results (again, SQL Server):

value | count | index
------+-------+------
foo   |     1 |     1
bar   |     3 |     1
bar   |     3 |     2
bar   |     3 |     3
baz   |     2 |     1
baz   |     2 |     2

score 2 · Answer 6 · answered May 03 '12 at 02:31

For appreciation only, SQL Server 2005 and later can handle this recursively:

declare @Stuff as Table ( Name VarChar(10), Number Int )
insert into @Stuff ( Name, Number ) values ( 'foo', 1 ), ( 'bar', 3 ), ( 'baz', 2 )

select * from @Stuff

; with Repeat ( Name, Number, Counter ) as (
  select Name, Number, 1
    from @Stuff
    where Number > 0
  union all
  select Name, Number, Counter + 1
    from Repeat
    where Counter < Number
  )
select *
  from Repeat
  order by Name, Counter -- Group by name.
  option ( maxrecursion 0 )

score 1 · Answer 7 · answered Jan 22 '17 at 10:42

By a simple JOIN you can reach to the aim of repeating records n times.
The following query repeats each record 20 times.

SELECT  TableName.*
FROM    TableName
JOIN    master.dbo.spt_values on type = 'P' and number < 20

Note for master.dbo.spt_values on type = 'P':
This table is used for getting a series of number which is hard-coded in it by condition of type='P'.

score 0 · Answer 8 · answered Jan 13 '18 at 11:46

You can use of CTE :

WITH Numbers(Num) AS
(
    SELECT 1 AS Num
    UNION ALL 
    SELECT Num + 1
    FROM   Numbers c
    WHERE  c.Num < 1000
)

SELECT VALUE,COUNT, number
FROM   TABLE
       JOIN Numbers
            ON  TABLE.count >= Numbers.Num
OPTION(MAXRECURSION 1000)

score 0 · Answer 9 · answered Feb 06 '18 at 13:39

In Oracle, we could use a combination of LEVEL and CROSS JOIN.

  SELECT *
    FROM yourtable
         CROSS JOIN (    SELECT ROWNUM index_t
                           FROM DUAL
                     CONNECT BY LEVEL <= (SELECT MAX (count_t) FROM yourtable))
   WHERE index_t <= count_t
ORDER BY VALUE, index_t;

DEMO

score 0 · Answer 10 · answered Apr 03 '23 at 11:49

0

WITH Numbers(Num) AS ( SELECT 1 AS Num UNION ALL SELECT Num + 1 FROM Numbers c WHERE c.Num < 1000 )

SELECT VALUE,COUNT, number FROM TABLE JOIN Numbers ON TABLE.count >= Numbers.Num OPTION(MAXRECURSION 1000)

answered Apr 03 '23 at 11:49

Vijay Arvindh

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 07 '23 at 18:48

SQL: Repeat a result row multiple times, and number the rows

10 Answers10

Linked

Related