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.

Solution 1

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

Solution 2

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

Solution 3

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

Solution 4

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.

Solution 5

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

View more solutions

Comments

[cygri]

Nice, it works, although I don't quite understand what it's doing :-)

[cygri]

Brilliant! I wish this worked everywhere. And yeah you're right about the reserved words, thanks for pointing out.

[Alexandre Leites]

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

[cygri]

I like this answer best because it's portable.

[vyegorov]

shouldn't there be a recursive keyword after the with ?

[Alexandre Leites]

@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 :-)

[Neels]

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. :)

[nzaleski]

Any insight into why this works? Worked great for my solution 4 years later btw.

[Justin Pihony]

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

[Bhanu Tez]

what is numbrs table?? is it system table?