Sql: Repeat a Result Row Multiple Times, and Number the Rows

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

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

Repeat Rows N Times According to Column Value

You could do that with a recursive CTE using UNION ALL:

;WITH cte AS
  (
    SELECT * FROM Table1

    UNION ALL

    SELECT cte.[ID], cte.ProductFK, (cte.[Order] - 1) [Order], cte.Price
    FROM cte INNER JOIN Table1 t
      ON cte.[ID] = t.[ID]
    WHERE cte.[Order] > 1
)
SELECT [ID], ProductFK, 1 [Order], Price
FROM cte
ORDER BY 1

Here's a working SQLFiddle.

Here's a longer explanation of this technique.

---

Since your input is too large for this recursion, you could use an auxillary table to have "many" dummy rows and then use SELECT TOP([Order]) for each input row (CROSS APPLY):

;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
      E02(N) AS (SELECT 1 FROM E00 a, E00 b),
      E04(N) AS (SELECT 1 FROM E02 a, E02 b),
      E08(N) AS (SELECT 1 FROM E04 a, E04 b),
      E16(N) AS (SELECT 1 FROM E08 a, E08 b)
SELECT t.[ID], t.ProductFK, 1 [Order], t.Price
FROM Table1 t CROSS APPLY (
  SELECT TOP(t.[Order]) N
  FROM E16) ca
ORDER BY 1

(The auxillary table is borrowed from here, it allows up to 65536 rows per input row and can be extended if required)

Here's a working SQLFiddle.

Repeat a result row multiple times by value in row

Create a numbers table and just do a CROSS JOIN with it

select Item , ItemName ,  Quantity
from yourtable A
cross join (select num from numbers) b
where a.Quantity <= b.num

To generate sequential numbers in Mysql check the below answers

• generate an integer sequence in MySQL
• Generating a range of numbers in MySQL

Repeat Rows N Times According to Column Value, Without Limit in Repeating

Finlay I found the solution. We can not use "OPTION(MAXRECURSION 0)" in CTE structure but we can use our query as a function and use "OPTION(MAXRECURSION 0)" in calling and running Function likes following:

Create fnCreateIndex
(  
  @Pr1 Int
)
RETURNS TABLE 
AS
RETURN 
(
WITH Numbers(Num) AS
(
  SELECT 1 AS Num
  UNION ALL 
  SELECT Num + 1
  FROM Numbers c
  WHERE c.Num < @Pr1), 
CTE as
(
 SELECT partname, qty, num
 FROM supplylist
 JOIN Numbers ON supplylist.qty >= Numbers.Num
)
 Select * from cte
)

Finaly we can use this for getting the resuls:

 select * from fnCreateIndex (50000)  order by partname, num OPTION(MAXRECURSION 0)

I found solution according to: https://stackoverflow.com/a/7428903/4885037

How to repeat the rows n times

Like this?

SQL> with test (a, b, c) as
  2    (select 1, 2, 3 from dual union all
  3     select 2, 3, 4 from dual
  4    ),
  5  temp as
  6    (select a, b, c,
  7       row_number() over (order by column_value, a) rn
  8     from test cross join table(cast(multiset(select level from dual
  9                                              connect by level <= 9
 10                                             ) as sys.odcinumberlist))
 11    )
 12  select a, b, c
 13  from temp
 14  where rn <= 9
 15  order by rn  ;

         A          B          C
---------- ---------- ----------
         1          2          3
         2          3          4
         1          2          3
         2          3          4
         1          2          3
         2          3          4
         1          2          3
         2          3          4
         1          2          3

9 rows selected.

SQL>

What does it do?

• lines #1 - 4 represent your sample data
• CTE temp (lines #5 - 11) created all those rows; row_number is used to "rank" them, ordered by column_value (think of it as of a level pseudocolumn, if it is closer to you) and the a column value (why? Your sample output suggests so)
• final query (lines #12 - 15) selects the result for rn <= 9 (as you wanted to get 9 rows)

Repeat each value n times as rows in SQL

Try this:

select * from names
cross join (select rownum n from dual
            connect by level <= (select max(repeat) from names))
where n <= repeat
order by name

How get a row multiple times repeated at SQL query result?

The IN operator just checks whether the value on the left matches some value on the right.
To return multiple rows, you have to either use multiple SELECTs, or create a temporary table where the value 4 appears in multiple rows:

WITH IDs_to_search(ID) AS (
    VALUES (2), (3), (4), (4), (5)
)
SELECT ID, text
FROM MyTable
JOIN IDs_to_search USING (ID);

t-sql repeat row numbers within group

You can use lag() to see where a person changes. Then use a cumulative sum:

select t.*,
       sum(case when prev_person = person then 0 else 1 end) over
           (partition by task_name order by timestamp) as desired_output
from (select t.*,
             lag(person) over (partition by task_name order by timestamp) as prev_person
      from t
     ) t ;

Note: I am interpreting your question as your wanting the numbers separately for each task ("every time a name changes in the task history").

EDIT:

Based on your comment:

select t.*,
       sum(case when prev_person = person and prev_stop_name = step_name then 0 else 1 end) over
           (partition by task_name order by timestamp) as desired_output
from (select t.*,
             lag(person) over (partition by task_name order by timestamp) as prev_person,
             lag(step_name) over (partition by task_name order by timestamp) as prev_step_name
      from t
     ) t ;

Repeating rows based on count in a different column - SQL

I would use a recursive CTE, but directly:

with cte as (
      select CID, CVID, count, 1 as ro
      from #temp1
      union all
      select CID, CVID, count, ro + 1
      from cte
      where cte.ro < cte.count
     )
select cte.*
from cte;

If your counts exceed 100, then you'll need to use option (maxrecursion 0).

Repeating rows based on column value in each row

Supposing you won't generate more than 1000 rows per row:

with num as (select level as rnk from dual connect by level<=1000)
select Job,  Quantity,   Status,  Repeat, rnk 
from t join num on ( num.rnk <= repeat )
order by job, rnk;

Here is a test:
http://sqlfiddle.com/#!4/4519f/12

UPDATE: As Jeffrey Kemp said, you can "detect" the maximum with a subquery:

with num as (select level as rnk 
             from dual 
             connect by level<=(select max(repeat) from t)
             )
select job,  quantity,   status,  repeat, rnk 
from t join num on ( num.rnk <= repeat )
order by job, rnk;

---

Related Topics