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 &gt; 0 THEN t.qty % 2 ELSE 2 END
FROM
    tableX AS t
  JOIN
    numbers AS n
  ON
    n.i &lt;= 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 &gt; 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 &lt;= t.qty ;


Tested at dbfiddle.uk.