In my case there are orders and order positions. Each order position has a quantity. For example:
But now I need a single row for each "position element". This is the output I want:
My idea is to use rank() / over() to get the incremental number, but I don't know how to use the quantity as multiplicator.
Is there a smart solution to use a single colum as "row multiplicator"? A sql function or a loop is not possible in my case, just plain sql :)
Thank you! :)
With the query from gvee I was able to create a solution for my problem:
Select
BelPosId as OrderPositionId,
Artikelnummer as ProductId,
Bezeichnung1 as ProductName,
Menge as Quantity,
NumberTable.number+1 as ElementId
FROM KHKVKBelegePositionen
INNER JOIN
(SELECT (a.number * 256) + b.number As number
FROM (
SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number <= 255
) As a
CROSS
JOIN (
SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number <= 255
) As b) NumberTable
ON
NumberTable.number < Menge
The trick was to inner join the column "quantity" to the "number" column from the numbers table with the less than operator to simulate a "multiplicator":
