Return batches of rows based on one row

Question

Say I have a table like this in SQL Server 2008:

id  |  name  |  qty
-------------------
1   |  john  |    1
2   |  bill  |    3
3   |  mary  |    2
4   |  jill  |    5

I would like to query this table and return 1 row for each batch of, at most, a quantity of 2. So, the result of the query would look like:

id  |  name  |  qty
-------------------
1   |  john  |    1
2   |  bill  |    2
2   |  bill  |    1
3   |  mary  |    2
4   |  jill  |    2
4   |  jill  |    2
4   |  jill  |    1

Can this be done neatly without using a cursor? Is this possible using unpivot?

By the way, the qty column can have a maximum value of 10.

ypercubeᵀᴹ · Accepted Answer · 2017-07-27T20:17:26.023

It's easy with a numbers table. Since also qty cannot be more than 10, we only need a very small numbers table:

CREATE TABLE numbers
  ( i int NOT NULL PRIMARY KEY ) ;

INSERT INTO numbers (i)
VALUES (1), (3), (5), (7), (9) ;

We need only the odd numbers because the numbers of rows wanted in the result is half of qty (or about half).

The query:

SELECT
    t.id, t.name,
    qty = CASE WHEN n.i = 1 AND t.qty % 2 > 0 THEN t.qty % 2 ELSE 2 END
FROM
    tableX AS t
  JOIN
    numbers AS n
  ON
    n.i <= t.qty ;

Of course we could also do it without any auxiliary table:

SELECT
    t.id, t.name,
    qty = CASE WHEN n.i = 1 AND t.qty % 2 > 0 THEN t.qty % 2 ELSE 2 END
FROM
    tableX AS t
  JOIN
    (VALUES (1), (3), (5), (7), (9)) AS n (i)
  ON
    n.i <= t.qty ;

Tested at dbfiddle.uk.

S4V1N · Answer 2 · 2017-07-27T22:11:14.187

You could also do it using function, and applying on the original table Such as:

CREATE FUNCTION NumbersTable (
  @fromNumber int,
  @toNumber int,
  @originalQty int
) RETURNS TABLE
RETURN (

WITH CTE_NumbersTable AS (
  SELECT case when @originalQty = @fromNumber then 1 else 2 end AS i,case when @originalQty = @fromNumber then 1 else 2 end AS i2
  UNION ALL
  SELECT case when i2 +2 < @toNumber then 2 else 1 end as i , i2 = i2 + case when i2 +2 <= @toNumber then 2 else 1 end 
  FROM CTE_NumbersTable
  WHERE
  i2 < @toNumber
)
SELECT i FROM CTE_NumbersTable
)

And then use it on the table:

select id,name,Quantity2.i as qty from TestNames
cross apply dbo.NumbersTable(1,qty,qty) as Quantity2

Return batches of rows based on one row

2 Answers2