Thread: Split one row in multiple row based upon a integer value

1. Registered User
Join Date
Jun 2014
Posts
3

Unanswered: Split one row in multiple row based upon a integer value

Hi everyone,

i got an issue and i don't know how to solve it.

My code looks like this for now:

Code:
```SELECT CDELIG.CDELIG_QTE AS [Quantity],
(ARTICLE.ART_PRIXPUBLIC * CDELIG.CDELIG_QTE) * 100  AS [Total line price],
(CDELIG.CDELIG_PU_SUBACC_MNT * CDELIG.CDELIG_QTE) * 100 AS [Grant]
FROM CDELIG
INNER JOIN ARTICLE ON ARTICLE.ART_ID = CDELIG.ART_ID```
And the result like this:

Quantity Total line price Grant
120 84000 0.0000
255 178500 0.0000
1 700 0.0000

The problem is that if a quantity is higher than 99 i got to split the row. And the others has to change also because the quantity would have change.

The expected result will be like this:

Quantity Total line price Grant
99 69300 0
21 14700 0
99 69300 0
99 69300 0
57 39900 0
1 700 0

2. Registered User
Join Date
Dec 2011
Location
Centurion, South Africa
Posts
20
Hello

You are having 3 columns (Quantity, Total line price, Grant) in your select query. Do you want to split it based on the quanity that an ART_ID has as I see;
1. 120 split into two 99 & 21; 84000 split into two 69300 & 14700
2. 255 split into three 99, 99, 57; 178500 split into three 69300, 69300 & 39900

I assume the quantity 120 belongs to one art_id and 255 to another art_id; and you want that the first split record to have 99, remaining quantity as another record and so on. So ideally you would be subtracting 99 from the total quantity & if still the remainder is greater than 99 then again subtracting 99 from the remainder quantity...right??? But then how will your manage subtracting the price in this process? Is there any other key that can separate the quantities for an art_id or do you want to add the first 99 quantity's price placing it against it in the same row and then next 99 quantity's price in the second row and so on...

Thanks & Regards
Satyajit

3. Registered User
Join Date
Jun 2014
Posts
3
You're right 120 QTE belongs to one art_id and 255 belongs to an other.

you want to add the first 99 quantity's price placing it against it in the same row and then next 99 quantity's price in the second row and so on...
This is exactly what i want to do !

Thanks & regards!

4. Registered User
Join Date
Apr 2012
Posts
213
Hi,

Try:

Code:
```with
CTE_Qty as
(
SELECT
CDELIG.ART_ID,
CDELIG.CDELIG_QTE AS [Quantity],
ARTICLE.ART_PRIXPUBLIC * 100 AS [Price],
CDELIG.CDELIG_PU_SUBACC_MNT * CDELIG.CDELIG_QTE * 100 AS [Grant]
FROM CDELIG
INNER JOIN ARTICLE
ON ARTICLE.ART_ID = CDELIG.ART_ID
),

CTE_Rec as
(
select
Art_Id,
case when Quantity > 99 then 99 else Quantity end as Quantity,
Quantity - 99 as Remainder,
Price,
[Grant]
from CTE_Qty

union all

select
Art_Id,
case when Remainder > 99 then 99 else Remainder end,
Remainder - 99,
Price,
[Grant]
from CTE_Rec
where
Remainder > 0
)

select
Art_Id,
Quantity,
Quantity * Price as TotalLinePrice,
[Grant]
from CTE_Rec
order by
Art_Id```
Hope this helps.

5. Registered User
Join Date
Dec 2011
Location
Centurion, South Africa
Posts
20
That should cater your needs with little modification to manage your calculations

gud one imex...

Cheers

6. Registered User
Join Date
Jun 2014
Posts
3
This is working like a charm!

Thank you very much Imex and Satyajit!

Cheers

Have a good day!

P.S: Is there a way to make the thread as Resolved or something?

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by Patly
Is there a way to make the thread as Resolved or something?
Sorry, but VBulletin is more forum software than a knowledgebase or help desk so there isn't any formal "resolved" status.