CODEWITHSUNDEEP
mysqlsqlsql-serveroracle-databasepostgresql
cygri
cygri

Reputation: 9482

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

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.

Upvotes: 28

Views: 70484

Answers (10)

Vijay Arvindh
Vijay Arvindh

Reputation: 1

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)

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

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

Upvotes: 0

Ebrahim Sabeti
Ebrahim Sabeti

Reputation: 51

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)

Upvotes: 0

Siyavash Hamdi
Siyavash Hamdi

Reputation: 3087

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

Upvotes: 1

Michael Buen
Michael Buen

Reputation: 39393

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

Upvotes: 7

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657122

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.

Upvotes: 8

Michael Buen
Michael Buen

Reputation: 39393

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

Upvotes: 25

HABO
HABO

Reputation: 15816

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 )

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280351

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

Upvotes: 4

Justin Pihony
Justin Pihony

Reputation: 67085

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

Upvotes: 33

Related Questions