August 25, 2017 at 1:42 pm
Here is the basic schema of table
CREATE TABLE Test (ID INT, ProductCode VARCHAR(50), Quantity INT)
INSERT INTO Test
SELECT 1,'ABC', 1
UNION
SELECT 2, 'DEF', 2
UNION
SELECT 3, 'GEH', 1
UNION
SELECT 4, 'XYZ', 3
This is the result I want. Basically, when quantity is greater than 1, I want additional lines to be returned by the query. So for e.g , if quantity is 2, i want two exact lines but quantity needs to be 1 and 1; if quantity is 3, I want 3 exactlines but quanity needs to be 1,1 and 1 as below.
Any idea would be much appreciated.
SELECT * FROM TEST
1 ABC 1
2 DEF 1
2 DEF 1
3 GEH 1
4 XYZ 1
4 XYZ 1
4 XYZ 1
August 25, 2017 at 1:57 pm
Wwhat about this?
WITH TEMP_CTE AS(
SELECT ID, ProductCode, Quantity - 1 AS Quantity FROM Test
UNION ALL
SELECT ID, ProductCode, Quantity - 1 FROM TEMP_CTE RECUR WHERE RECUR.Quantity > 0
)
SELECT ID, ProductCode, 1 FROM TEMP_CTE
ORDER BY ID
August 25, 2017 at 2:03 pm
Brilliant!!! Thanks.
August 25, 2017 at 2:12 pm
If quantities get to 100 or more, you are likely to blow your max recursion limit & will need to use a different technique.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 29, 2017 at 7:19 am
Phil Parkin - Friday, August 25, 2017 2:12 PMIf quantities get to 100 or more, you are likely to blow your max recursion limit & will need to use a different technique.
Just an FYI, you can control that limit using OPTION (MAXRECURSION n))
The valid values for n are from 0 to 32K, and 0 means unlimited. Of course, specify 0 and it becomes possible to enable an infinite loop.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 29, 2017 at 7:29 am
sgmunson - Tuesday, August 29, 2017 7:19 AMPhil Parkin - Friday, August 25, 2017 2:12 PMIf quantities get to 100 or more, you are likely to blow your max recursion limit & will need to use a different technique.Just an FYI, you can control that limit using
OPTION (MAXRECURSION n))
The valid values for n are from 0 to 32K, and 0 means unlimited. Of course, specify 0 and it becomes possible to enable an infinite loop.
Thanks Steve. I used the word 'likely' because I know that it can be changed.
But personally, I prefer sticking at 100 and using other techniques where this limit would be exceeded.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 31, 2017 at 3:37 pm
Another option
;with dummy as
(
select ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rowid
from string_split(SPACE(1000),' ')
)
select ord.ID, ord.ProductCode, 1 Qty
from #Test ord
inner join dummy d on d.rowid<=ord.Quantity
order by ord.id
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply