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

You could use a numbers table

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

Here is a SQLFiddle using MSSQL

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_16AS 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_256AS SELECT ( ( hi.n * 16 ) + lo.n ) AS n     FROM generator_16 lo, generator_16 hi;CREATE VIEW generator_4kAS SELECT ( ( hi.n * 256 ) + lo.n ) AS n     FROM generator_256 lo, generator_16 hi;CREATE VIEW generator_64kAS SELECT ( ( hi.n * 256 ) + lo.n ) AS n     FROM generator_256 lo, generator_256 hi;CREATE VIEW generator_1mAS 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.nFROM tbl tJOIN generator_64k i ON i.n between 1 and t.cntorder 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

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 indexFROM   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.