List Lines per Quantity

Question

I'm probably over thinking this. I have a simple table with Name and Ticket Quantity columns. I want to output a row by row list of the names for each quantity purchased. See example below.

Table:

Name           Quantity
-----------------------
Bob            1
Joe            2
Sally          1

Output:

Bob
Joe
Joe
Sally

How could I achieve this in TSQL?

@DanAndrews - [Recursive CTEs are slow](http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232). Creating a permanent numbers table and `SELECT Name FROM Names JOIN Numbers ON Number <= Quantity` would be better. — Martin Smith, Feb 20 '14 at 15:34
@MartinSmith Careful talking bad about CTEs. They solve every problem ever conceived. — cadrell0, Feb 20 '14 at 16:03

SQLMason · Accepted Answer · 2014-02-21T18:28:30.947

SETUP:

DECLARE @table TABLE (
    NAME VARCHAR(10),
    Quantity INT
    )

INSERT INTO @table
SELECT 'Bob',   1 UNION ALL
SELECT 'Joe',   2 UNION ALL
SELECT 'Sally', 1

Recursive CTE

;WITH Members (
    NAME,
    Quantity
    )
AS (
    -- Base case
    SELECT NAME,
        Quantity
    FROM @table

    UNION ALL

    -- Recursive
    SELECT NAME,
        Members.Quantity - 1
    FROM Members
    WHERE Members.Quantity > 1
    )
SELECT NAME
FROM Members
OPTION (MAXRECURSION 0)
ORDER BY 1

Result:

Bob
Joe
Joe
Sally

Alternatively you could (per @Martin Smith's suggestion):

DECLARE @numbers TABLE (number INT)

INSERT INTO @numbers (number)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

Finally:

SELECT NAME
FROM @table t
INNER JOIN @numbers n ON n.number <= t.Quantity
ORDER BY 1

Result:

Bob
Joe
Joe
Sally

And if you really like recursive CTE's (because they smell good), you could build your numbers table with a recursive CTE. You should be using physical tables and not variable tables as you see here - so that you don't have to build them every time.

;WITH Numbers (Value)
AS (
    -- Base case
    SELECT 32767 Value

    UNION ALL

    -- Recursive
    SELECT Numbers.Value - 1
    FROM Numbers
    WHERE Numbers.Value > 1
    )
INSERT INTO @numbers (number)
SELECT Value
FROM Numbers
OPTION (MAXRECURSION 32767)

List Lines per Quantity

1 Answers1

Linked