3

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.

8

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.

1

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.