Splitting row into multiple records based on row value I have an Orders table (text customer_id, int total_orders) which contains overview stats of total orders for each customer.



What I am trying to do is generate a query output that will represent a customer as a row for each third order they've made similar to this.



The combined_key is used as the primary/unique key which is simply the customer_key and count combined as I cannot use an auto-number.

I know how I can identify which records I need to be in the query but I don't know how I can split the records as desired.

select *
from stats
where order_count / 3


I've included a fiddle, please note it's in MySQL because T-SQL is not available.
You can use db&lt;>fiddle for sql-server:

https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=c87b972491dda6462cc4f8b80ff2bd95

This is just a sketch. You need to generate something out of nothing (1 row -> 3 rows). For this you can use a recursive CTE:

with gen (account_id, n, order_count) as (
    select account_id, 1, order_count from stats
    union all
    select account_id, n+1, order_count
    from gen where n &lt; order_count
)
select n-2 as x, n as y, account_id, n
from gen
where n % 3 = 0
order by account_id, n;


I used mod(%) to pick each 3:rd row.

x   y   account_id  n
1   3   1   3
4   6   1   6
7   9   1   9
1   3   2   3