Query Help

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  August 25, 2017 at 1:42 pm

  #338817

  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

• ZZartin

  SSC-Dedicated

  Points: 30921

  More actions

  August 25, 2017 at 1:57 pm

  #1956942

  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

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  August 25, 2017 at 2:03 pm

  #1956943

  Brilliant!!! Thanks.

• Phil Parkin

  SSC Guru

  Points: 247110

  More actions

  August 25, 2017 at 2:12 pm

  #1956947

  If quantities get to 100 or more, you are likely to blow your max recursion limit & will need to use a different technique.

  ---

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  August 29, 2017 at 7:19 am

  #1957227

  Phil Parkin - Friday, August 25, 2017 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.

  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)

• Phil Parkin

  SSC Guru

  Points: 247110

  More actions

  August 29, 2017 at 7:29 am

  #1957231

  sgmunson - Tuesday, August 29, 2017 7:19 AM

  Phil Parkin - Friday, August 25, 2017 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.

  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.

  ---

• pietlinden

  SSC Guru

  Points: 63551

  More actions

  August 29, 2017 at 5:22 pm

  #1957339

  With a tally table, this is pretty easy:

  DECLARE @max-2 INT;
SELECT @max-2 = MAX(Quantity) FROM #Test;

  SELECT ID
    , ProductCode
    , Quantity
FROM #Test te
INNER JOIN Scratchpad.dbo.TallyN(@Max) t
ON t.N <= te.Quantity;

  No recursion required, right?

• Avi1

  SSCrazy

  Points: 2323

  More actions

  August 31, 2017 at 3:37 pm

  #1957756

  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