Stefan Brendle
Stefan Brendle

Reputation: 1564

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

In my case there are orders and order positions. Each order position has a quantity. For example:

enter image description here

But now I need a single row for each "position element". This is the output I want:

enter image description here

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

enter image description here

Upvotes: 8

Views: 25194

Answers (3)

Szymon
Szymon

Reputation: 43023

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

Upvotes: 7

gvee
gvee

Reputation: 17161

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

Upvotes: 6

Igor Borisenko
Igor Borisenko

Reputation: 3866

You can do it creating table with numbers:

/* table variable is used just for example. 
   It's better to create table and fill it with numbers once*/   

declare @numbers table(i int)

declare @i int=1

while @i<100
begin
    insert into @numbers values (@i)

    set @i=@i+1
end

declare @your_table table (N int)

insert into @your_table values (10),(1),(5)


select yt.N, n.i
from @your_table yt
     join @numbers n ON n.i<=yt.N
order by yt.N, n.i

Upvotes: 0

Related Questions