# Return batches of rows based on one row

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.

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.

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
``````