CODEWITHSUNDEEP
sql-serverasp.net-mvc-4
Salim Latif Waigaonkar
Salim Latif Waigaonkar

Reputation: 472

Split one record in to multiple record based on quantity in sql server in MVC 4

Here my table Asset having one record having quantity greater than 1 enter image description here

when i click on split button then i want to having each single entry i.e if quantity is 3 then i want to divide cost with 3 and add new two entry same table and update old entry , following image show excepted result

enter image description here

Upvotes: 0

Views: 93

Answers (2)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

You can try this -

DECLARE @MyAsset TABLE 
  (
    [AssetId] INT,
    [AssetName] Varchar(20),
    [Qty] int
  )

INSERT @MyAsset
    VALUES (1,'Computer', 3),
           (2,'Printer', 2)


SELECT
    [AssetId]
    ,[AssetName]
    ,1 AS [Qty]
FROM @MyAsset t
INNER JOIN master..spt_values num
    ON num.type = 'p'
    AND num.number < t.Qty

Result

AssetId AssetName   Qty
------------------------
1       Computer    1
1       Computer    1
1       Computer    1
2       Printer     1
2       Printer     1

Upvotes: 1

Squirrel
Squirrel

Reputation: 24803

using a tally or number table. Here I am using recursive CTE to dynamically create one

; with number as
(
    select  n = 1
    union all
    select  n = n + 1
    from    number
    where   n < 99
)
select  a.*,
        SplitCost = a.Cost / a.Quantity,
        SplitQty  = 1
from    @Asset a
        inner join number n on  n.n <= a.Quantity

Note : no matter what is your data type for your cost column, after you dividing it by quantity, if you add up all the individual cost after splitting, you might not get back your original value.

Upvotes: 1

Related Questions