SQLite Forum

Repeat values n times
Login

Repeat values n times

(1) By Murtaza (murtazanw) on 2021-07-07 08:38:03 [link] [source]

I have below table;

sku,qty
-------
item1,3
item2,2
item3,5
item4,4

I want values of sku column to be repeatedly inserted n number of times specified in qty column so the result looks like below;

sku,qty
-------
item1,3
item1,3
item1,3
item2,2
item2,2
item3,5
item3,5
item3,5
item3,5
item3,5
item4,4
item4,4
item4,4
item4,4

I checked and read about the recursive command table expression but find difficult to practically implement it. I want to learn basics of it.

(2.3) By mzm2021 on 2021-07-07 09:18:08 edited from 2.2 in reply to 1 [link] [source]

Assuming the sku/qty table is named input, here is a possible solution:

WITH RECURSIVE counter(value) AS (
   SELECT 1
     UNION ALL
   SELECT value + 1 FROM counter LIMIT (SELECT MAX(qty) FROM input)
)
SELECT * FROM input JOIN counter ON value <= qty ORDER BY sku, qty, value

The counter CTE will generate a sequence of integers value from 1 to the maximum qty. It is then joined with table input on value <= qty.

Using SQLite's specific table-valued function generate_series(), the recursive CTE could be removed as in:

SELECT * FROM input JOIN generate_series(1, (SELECT MAX(qty) FROM input)) ON value <= qty ORDER BY sku, qty, value

By the way, the ORDER BY clause is not required. It is only meant to help you inspect the result and check how each query works.

(3) By Gunter Hick (gunter_hick) on 2021-07-07 09:10:18 in reply to 1 [link] [source]

What makes you think you need repeated identical records in a table? What is the difference between individual repeated records?

(4) By Ryan Smith (cuz) on 2021-07-07 12:29:27 in reply to 1 [link] [source]

find difficult to practically implement it. I want to learn basics of it.

There's been a quite satisfactory answer already, so this is just some thoughts added to it, since the CTE addition is one of my all-time favourite things in SQL, and specifically, in SQLite.

Note that this explanation is for basic use and basic understanding, there is a lot more to CTEs and more correct terms, but this is the plain English simplified version.

In a recursive common table expression there are two queries, the initial query that establishes the first value(s) and the recursed query that builds on the output repeatedly, until the LIMIT is reached or the WHERE clause yields FALSE.

To tell the query we will be using CTEs, we start with the words "WITH" or "WITH RECURSIVE". (The "Recursive" is optional in many SQL engines, including SQLite, but it helps show your intent to a next person who reads the query). The CTE statements start with a name for the CTE which can be used later in the normal query (which follows after the CTE section), an optional section in parentheses to name the columns, the keyword "AS" and the CTE query, again in parentheses - like this:

WITH RECURSIVE
CTEName(col1, col2, ...) AS (

  ... Initial CTE Query here ...

  UNION ALL

  ... Recursive CTE Query here ...

)
... The rest of the normal Query here ...

The initial query does nothing more than establish the first or base values to work from, and the second (recursive) query selects from the output of the CTE's output, selecting from itself, hence the term "Recursive". The two queries are union-ed together by the UNION ALL clause.

That's the entire structure, no more, no less. It's simple and elegant. To see how it works, we can try some simple things:

Make a counter that outputs a list of numbers, let's say the numbers from 5 to 12, in a column named "ExampleNo".

First step is to think about the process. We need to start from 5 (that answers our question of what to select for the initial query inside the CTE), then we need to select it, and the value after it (itself + 1) and continue adding 1 until we reach 12. We need to name the CTE something useful (let's say "CountCTE") and have its one and only column named "ExampleNo" so that we can refer to it in the normal query at the end.

That CTE might look like this:

WITH RECURSIVE
CountCTE(ExampleNo) AS (        -- CTE Definition
    SELECT 5                    -- Initial Query
    UNION ALL
    SELECT ExampleNo + 1        -- Recursive Query
      FROM CountCTE             -- Selected from itself (recursion)
     WHERE ExampleNo < 12       -- Stop when ExampleNo >= 12
)
SELECT ExampleNo                -- Normal Query
  FROM CountCTE                 -- using the above CTE
;

  -- The output:

  --   ExampleNo 
  --   ---------
  --       5     
  --       6     
  --       7     
  --       8     
  --       9     
  --      10     
  --      11     
  --      12     

That's all there is to it.

How it works: The initial query produces one line of output, imagine that it goes to sit in a FIFO buffer list ready to send to the output. Before it gets output to the normal query though, the recursive query has an opportunity to read any new rows added to the output buffer and add any new SELECT output rows according to its own SELECT clause. So on the first iteration the recursive query reads the 5 that is already in the buffer, does its own select operation (SELECT ExampleNo + 1 ...) which yields 6, and then adds that to the output buffer list.

But now, since there is a new item in the output buffer (6), the recursive query again has the opportunity to first read it and use it in the recursive select, this time yielding 7. Again there is a new item in the buffer, and again the recursive query reads it... etc. etc. and this recursion will continue into infinity if we do not stop it at some point. Luckily when we get to 12, the WHERE clause says that we can only select values from the buffer where ExampleNo < 12... so this time the recursive query yields no lines, so we do not output anything to the buffer, so there is no new buffer lines to read, and the recursion ends there.

Note that the query stops at ExampleNo < 12, which means the last ExampleNo to be processed inside the recursive query is 11, but the output is (SELECT ExampleNo + 1 ...) and therefore the last output we see coming out of it is indeed 11 + 1, which is the 12 we see.

Take care to not state queries that will go into infinite loops. Another good way to ensure the loop does not last forever is to use a LIMIT clause, which in our query should be 7 since we needed the 7 numbers (5..12) which could make our query look like this:

WITH RECURSIVE
CountCTE(ExampleNo) AS (
    SELECT 5
    UNION ALL
    SELECT ExampleNo + 1
      FROM CountCTE
     LIMIT 7
)
SELECT ExampleNo
  FROM CountCTE
;

How to limit the output depends - if we knew we wanted exactly 20 lines of output, regardless what they are, then LIMIT is best. If we wanted the last output line to be an exact value, regardless of how many lines it takes to reach that value, then a WHERE clause is best. You are also welcome to combine them, perhaps as a way to ensure avoiding infinite loops.

Lastly, I've only shown queries above using one column, but you can use multiple columns and do recursive calculation on any and all of them as long as you remember the idea of whatever goes into the output buffer will be passed through the recursive query and may produce more output buffer items. Also, the initial query may indeed be a full complex query with multiple items, perhaps even from another table.

Here are some quick example use cases:

  • 1. Get a list of 5th multiples, 5th modulos, and squares of the first 20 numbers:
WITH RECURSIVE
MathCTE(Num, Fifth_Multi, Fifth_Mod, Square) AS (
    SELECT 0 ,0 ,0, 0
    UNION ALL
    SELECT Num + 1,  Num * 5,  Num % 5,  Num * Num
      FROM MathCTE WHERE Num < 20
)
SELECT * FROM MathCTE WHERE Num > 0
;

  --             | Fifth_- |         |      
  --      Num    |  multi  |Fifth_Mod|Square
  -- ------------|---------|---------|------
  --       1     |    0    |    0    |   0  
  --       2     |    5    |    1    |   1  
  --       3     |    10   |    2    |   4  
  --       4     |    15   |    3    |   9  
  --       5     |    20   |    4    |  16  
  --       6     |    25   |    0    |  25  
  --       7     |    30   |    1    |  36  
  --       8     |    35   |    2    |  49  
  --       9     |    40   |    3    |  64  
  --      10     |    45   |    4    |  81  
  --      11     |    50   |    0    |  100 
  --      12     |    55   |    1    |  121 
  --      13     |    60   |    2    |  144 
  --      14     |    65   |    3    |  169 
  --      15     |    70   |    4    |  196 
  --      16     |    75   |    0    |  225 
  --      17     |    80   |    1    |  256 
  --      18     |    85   |    2    |  289 
  --      19     |    90   |    3    |  324 
  --      20     |    95   |    4    |  361 

Note that I start with 0,0,0,0 in the initial query, and then in the normal query I simply excluded that result. This allows me to make the CTE do all the work and not needing to calculate the first query by hand - which is not just better for lazy people like me, but also safer in ensuring consistency.

  • 2. Print all dates for the next 15 days from today:
WITH RECURSIVE
Days(DayDate) AS (
    SELECT date('now')
    UNION ALL
    SELECT date(DayDate,'+1 day') FROM Days LIMIT 15
)
SELECT DayDate,
       strftime('%Y',DayDate) AS Year,
       strftime('%W',DayDate) AS WeekOfYear,
       strftime('%w',DayDate) AS DayOfWeek
  FROM Days
;


  --             |      |WeekOfYe-|         
  -- DayDate     | Year |    ar   |DayOfWeek
  -- ------------|------|---------|---------
  -- 2021-07-07  | 2021 |    27   |    3    
  -- 2021-07-08  | 2021 |    27   |    4    
  -- 2021-07-09  | 2021 |    27   |    5    
  -- 2021-07-10  | 2021 |    27   |    6    
  -- 2021-07-11  | 2021 |    27   |    0    
  -- 2021-07-12  | 2021 |    28   |    1    
  -- 2021-07-13  | 2021 |    28   |    2    
  -- 2021-07-14  | 2021 |    28   |    3    
  -- 2021-07-15  | 2021 |    28   |    4    
  -- 2021-07-16  | 2021 |    28   |    5    
  -- 2021-07-17  | 2021 |    28   |    6    
  -- 2021-07-18  | 2021 |    28   |    0    
  -- 2021-07-19  | 2021 |    29   |    1    
  -- 2021-07-20  | 2021 |    29   |    2    
  -- 2021-07-21  | 2021 |    29   |    3    

  • 3. Lastly, expanding on all the previous examples, some more technical example. Here we have a made-up table of event dates, but we want to display them in a calendar format, so we need to show days for which there are no calendar entries. This seemingly simple request is impossible to do without a full calendar date table or a recursive CTE. For size consideration, it is limited to the month of February, 2021. It also shows off how to use multiple CTEs in one query, how CTE's can be joined to other tables (and other CTEs), and the use of the VALUES function to make a CTE table from constants.

Please note that these are by far not the only ways or necessarily best ways to do everything, but it does show the concepts in action.

CREATE TABLE Events(
  EventDate NUMERIC,
  EventDescription TEXT
);

INSERT INTO Events VALUES
 ('2021-02-13','John''s birthday')
,('2021-02-14','Valentine''s day')
,('2021-02-18','Fishing Trip with Jesse')
,('2021-02-27','Deadline - Artemis project')
;

WITH RECURSIVE
Days(DayDate) AS (
    SELECT date('2021-02-01')         -- Start of February
    UNION ALL
    SELECT date(DayDate,'+1 day')
      FROM Days
     WHERE DayDate < '2021-02-28'     -- End of February
     LIMIT 31
), 
WeekDays (DayNo, DayName) AS (
    VALUES (0, 'Sunday'),             -- Constants specified as VALUES
	   (1, 'Monday'),
	   (2, 'Tuesday'),
	   (3, 'Wednesday'),
	   (4, 'Thursday'),
	   (5, 'Friday'),
	   (6, 'Saturday')
)
SELECT WeekDays.Dayname,  Days.DayDate,  IFNULL(Events.EventDescription,'') AS Event
  FROM Days
  JOIN WeekDays ON WeekDays.DayNo = CAST(strftime('%w',DayDate) AS INT)
  LEFT JOIN Events ON Events.EventDate = Days.DayDate
  ORDER BY Days.DayDate
;


  -- DayName    |DayDate     |Event                       
  -- -----------|------------|----------------------------
  -- Monday     |2021-02-01  |                            
  -- Tuesday    |2021-02-02  |                            
  -- Wednesday  |2021-02-03  |                            
  -- Thursday   |2021-02-04  |                            
  -- Friday     |2021-02-05  |                            
  -- Saturday   |2021-02-06  |                            
  -- Sunday     |2021-02-07  |                            
  -- Monday     |2021-02-08  |                            
  -- Tuesday    |2021-02-09  |                            
  -- Wednesday  |2021-02-10  |                            
  -- Thursday   |2021-02-11  |                            
  -- Friday     |2021-02-12  |                            
  -- Saturday   |2021-02-13  |John's birthday             
  -- Sunday     |2021-02-14  |Valentine's day             
  -- Monday     |2021-02-15  |                            
  -- Tuesday    |2021-02-16  |                            
  -- Wednesday  |2021-02-17  |                            
  -- Thursday   |2021-02-18  |Fishing Trip with Jesse     
  -- Friday     |2021-02-19  |                            
  -- Saturday   |2021-02-20  |                            
  -- Sunday     |2021-02-21  |                            
  -- Monday     |2021-02-22  |                            
  -- Tuesday    |2021-02-23  |                            
  -- Wednesday  |2021-02-24  |                            
  -- Thursday   |2021-02-25  |                            
  -- Friday     |2021-02-26  |                            
  -- Saturday   |2021-02-27  |Deadline - Artemis project  
  -- Sunday     |2021-02-28  |                            

Hope that offers some clarity, and Good luck!

(5) By Murtaza (murtazanw) on 2021-07-07 14:43:21 in reply to 2.3 [source]

@mzm2021,

Thanks you so much dear for your kind help. Your explanation is up to the point.

I got both the methods working very well for me. Both methods are formed of two parts as one is actual data to be fetched from the table and the other part triggers the repetition of running the query for that matter.

I understand that;

WITH RECURSIVE counter(value) AS (SELECT 1 UNION ALL SELECT value + 1 FROM counter LIMIT (SELECT MAX(qty) FROM input))

is the recursive counter on the select statement to run for each row and;

SELECT * FROM input JOIN counter ON value <= qty;

is the actual query to fetch the data from the table.

The generate_series function is quite handy, easy and quick to understand as well. It's impacting the query by the counter with JOIN command. Absolutely amazing.

This did solve my problem.

(6) By Murtaza (murtazanw) on 2021-07-07 14:59:53 in reply to 3 [link] [source]

Dear Gunter Hick,

I needed the identical values repeated for n number of times for the output values to be printed exactly for n numbers.

Let me clarify further.

I have a list of skus (items) and the qty in one table (received sales orders). Now based on qty I want to print that many labels of each sku. This list serves as a source table (in mail merge) for the printouts. Now I have to pass print command for a particular sku and select copies as per qty to make that many copies of the labels. So I chose to change the source table to be containing exactly that number of skus which I want to print, note that skus will be repeated based on their qty. In this way I don't have to select the copies for each sku separately. I can directly give print command without thinking of each sku. So I put all this into sqlite script, run it, populate the target table with all skus.

This is kind of automation to save time and increase the work flow.

(7) By Gunter Hick (gunter_hick) on 2021-07-08 06:35:19 in reply to 6 [link] [source]

That now makes sense. Stating the business case "I want to print labels for the items of an order" woudl have provided the necessary context.

(8) By tom (younique) on 2021-08-09 11:52:15 in reply to 4 [link] [source]

Thanks for this great explanation!