How to multiply a single row with a number from column in sql

Question

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":

Answer 1

You need to join to a numbers table!

CREATE TABLE dbo.numbers (
   number int NOT NULL
)

ALTER TABLE dbo.numbers
ADD
   CONSTRAINT pk_numbers PRIMARY KEY CLUSTERED (number)
     WITH FILLFACTOR = 100
GO

INSERT INTO dbo.numbers (number)
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

Here's where I keep my latest script: http://gvee.co.uk/files/sql/dbo.numbers%20&%20dbo.calendar.sql

Once you have this in place you perform a simple join:

SELECT KHKVKBelegePositionen.BelPosId As OrderPositionId
     , KHKVKBelegePositionen.Artikelnummer As ProductId
     , KHKVKBelegePositionen.Bezeichung1 As ProductName
     , KHKVKBelegePositionen.Menge As Quantity
     , numbers.number As ElemendId
FROM   KHKVKBelegePositionen
 INNER
  JOIN dbo.numbers
    ON numbers.number BETWEEN 1 AND KHKVKBelegePositionen.Menge

Answer 2

You can use that script which doesn't require any external tables:

SELECT t.quantity, 
       n.num 
FROM   table10 t 
       INNER JOIN (SELECT Row_number() 
                            OVER( 
                              ORDER BY object_id) num 
                   FROM   sys.all_objects) n 
               ON t.quantity >= n.num 

I didn't include other columns but you can just add them to the select list