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

i have sql query result this:

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

now want expand each row count larger 1 occurs multiple times. need these rows numbered. get:

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

i have make work on major databases (oracle, sql server, mysql, postgresql, , maybe more). solution works across different databases ideal, clever ways make work on database appreciated.

for mysql, use poor man's generate_series, done via views. mysql rdbms among big four don't has cte feature.

actually can use technique on database supports view. that's virtually database

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

the minor modification made replace bitwise (shift left , bitwise or) technique original technique mere multiplication , addition respectively; sql server , oracle has no shift left operator.

this abstraction 99% guaranteed work on database, except oracle; oracle's select can't function without table, in order this, 1 need select dummy table, oracle provided 1 already, it's called dual table. database portability pipe dream :-)

here's abstracted views works on rdbms, devoid of bitwise operations(which not necessity anyway in scenario) , feature nuances(we remove or replace on create view, postgresql , mysql supports them) among major database.

oracle caveat: put from dual after each select expression

create view generator_16 select 0 n union select 1  union select 2  union     select 3   union select 4  union select 5  union    select 6   union select 7  union select 8  union    select 9   union select 10 union select 11 union    select 12  union select 13 union select 14 union     select 15;  create view generator_256 select ( ( hi.n * 16 ) + lo.n ) n      generator_16 lo, generator_16 hi;  create view generator_4k select ( ( hi.n * 256 ) + lo.n ) n      generator_256 lo, generator_16 hi;  create view generator_64k select ( ( hi.n * 256 ) + lo.n ) n      generator_256 lo, generator_256 hi;  create view generator_1m select ( ( hi.n * 65536 ) + lo.n ) n      generator_64k lo, generator_16 hi; 

then use query:

select t.value, t.cnt, i.n tbl t join generator_64k  on i.n between 1 , t.cnt order 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