Search code examples
sqlsql-serverunpivot

SQL Server Split Row based Subtraction value

I'm not sure whether my question is correct or not, but i want to generate output based below input

Input data

then split the data based on karton quantity. Below is the output.

Is it possible to make this happen using SQL? Its like unpivot the quantity based on

karton quantity.

Output

or output like this.

enter image description here

Solution

  • Please try:

    ;with T as(
    select *, 1 as RNum, Quantity/Karton Split 
    from tbl

    union all

    select 
        T.Num, T.Quantity-T.Karton, T.Karton, T.RNum+1, T.Split 
    from 
        T join tbl T1 on T.Num=T1.Num 
    where 
        T.Split>=1 and T.Quantity-T.Karton>0
)
select Num, Quantity, Karton from T  order by Num, Quantity Desc

    Fiddle Demo here