Q:

SQL JOIN table data with incremental variable with max value

I'm wondering on how to get a join query of two auxiliary tables to get a complete set of production line test slots "names" or "tags"
The first auxiliar table has information on how many slots a certain machine has. And it looks like
Machine Types AUX table
------------------------------------------------------
MachineTypeID   SlotNumber
1               5
2               3
3               10

The second one has the list of machine IDs for each production line. Looks like:
Lines AUX table
----------------------------------
LineID   MachineID   MachineTypeID
1        PR00231     1
1        PR00049     2
2        PR00147     1
2        PR00017     2
2        PR00178     3

I don't know how to build a list of "MachineID+SlotNum" tag using the list of MachineIDs and the number of slots that each machine has.
Expected result, when filtering by line, would be like:
LINE 2 list of slotsTAGs:
-----------------------------------
PR00147_Slot1
PR00147_Slot2
PR00147_Slot3
PR00147_Slot4
PR00147_Slot5
PR00017_Slot1
PR00017_Slot2
PR00017_Slot3
PR00178_Slot1
PR00178_Slot2
PR00178_Slot3
PR00178_Slot4
PR00178_Slot5
PR00178_Slot6
PR00178_Slot7
PR00178_Slot8
PR00178_Slot9
PR00178_Slot10

Thanks!

A:

To generate this result, you need to form a numbers table which consists of the numbers from 1 to MAX(SlotNumber). In this query I've used a recursive CTE to do that. This can then be JOINed to the Lines and Types table to form the list:
WITH cte1 AS (
SELECT  MAX(SlotNumber) AS MaxSlot FROM Types
), cte2 (Slot) AS 
(SELECT 1 AS Slot
 UNION ALL
 SELECT Slot+1 FROM cte2 WHERE Slot < (SELECT MaxSlot FROM cte1))
SELECT CONCAT(l.MachineID, '_Slot', c.Slot) AS Slots
FROM Lines l
JOIN Types t ON t.MachineTypeID = L.MachineTypeID
JOIN cte2 c ON c.Slot <= t.SlotNumber
WHERE l.LineID = 2
ORDER BY l.MachineId, c.Slot

Output:
Slots
PR00017_Slot1
PR00017_Slot2
PR00017_Slot3
PR00147_Slot1
PR00147_Slot2
PR00147_Slot3
PR00147_Slot4
PR00147_Slot5
PR00178_Slot1
PR00178_Slot2
PR00178_Slot3
PR00178_Slot4
PR00178_Slot5
PR00178_Slot6
PR00178_Slot7
PR00178_Slot8
PR00178_Slot9
PR00178_Slot10

Demo on dbfiddle

