Split a single row into multiple row based on column value

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have following quantity table 

<pre>Item	QuantityRequired	MaxQuantity
 Item1	      200	               50
 Item2	      100	               30

Based on MaxQuantity column value, table rows has to get split like below,

Item	QuantityRequired
Item1	  50
Item1	  50
Item1	  50
Item1	  50
Item2	  30
Item2	  30
Item2	  30
Item2	  10

What I have tried:

I treid with union its not working.

Posted 24-Apr-20 4:39am

Member 14636607

Updated 24-Apr-20 6:15am

Add a Solution

Comments

CHill60 24-Apr-20 11:18am

Show the code you tried

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2020-04-24T05:44:00

I chose to use a recursive CTE (Common Table Expression) to do this, combined with another CTE

SQL

;with cte1 as
(
	select item, quantityrequired, maxquantity, 
	cast(QuantityRequired / maxquantity as integer) as wholes, QuantityRequired % MaxQuantity as partials
	from #test
)
,cte2 as 
(
    SELECT item, maxquantity, wholes, 'base ' as w from cte1
    UNION ALL
    SELECT item, maxquantity, wholes - 1, 'recur' as w
    FROM cte2 WHERE wholes > 1
)
SELECT item, MaxQuantity as QuantityRequired 
from cte2
union all
SELECT item, partials as QuantityRequired 
from cte1 where partials > 0
order by Item, QuantityRequired desc

Edit - meant to explain this before I submitted it!

The first CTE is determining how many rows I need, wholes is the number of rows where I can use the maximum quantity allowed (required / max), partialsis what is left over (required mod max)

The 2nd CTE generates all of the 'whole' rows needed - I included that column w to help you see what was happening (if you want to include it in the select) - a full explanation of recursive CTEs can be found at Recursive CTEs Explained - Essential SQL[^]

The final select takes all of those "whole" rows and unions with the left-over "partial" value for each item - if there is one. Note the use of Union all so the duplicate "whole" rows are not removed

Richard Deeming · Accepted Answer · 2020-04-24T06:17:00

Here's a solution with a single recursive CTE[^]:

SQL

WITH cte As
(
    SELECT
        Item,
        CASE
            WHEN QuantityRequired > MaxQuantity THEN MaxQuantity
            ELSE QuantityRequired
        END As QuantityRequired,
        QuantityRequired - MaxQuantity As RemainingQuantity,
        MaxQuantity
    FROM
        YourTable
    
    UNION ALL
    
    SELECT
        Item,
        CASE
            WHEN RemainingQuantity > MaxQuantity THEN MaxQuantity
            ELSE RemainingQuantity
        END As QuantityRequired,
        RemainingQuantity - MaxQuantity As RemainingQuantity,
        MaxQuantity
    FROM
        cte
    WHERE
        RemainingQuantity > 0
)
SELECT
    Item,
    QuantityRequired
FROM
    cte
ORDER BY
    Item
;

Output:

Item    QuantityRequired
------------------------
Item1	50
Item1	50
Item1	50
Item1	50

Item2	30
Item2	30
Item2	30
Item2	10

Split a single row into multiple row based on column value

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0