Returning repeating number of rows in SQL

Question

I have a table like this:

|---------|-----|
| Name    | Qty |
|---------|-----|
| Joe     | 3   |
| Bill    | 2   |
| Mike    | 4   |
|---------|-----|

I am trying to create a SELECT statement that will return this:

|--------|
| Name   |
|--------|
| Joe    |
| Joe    |
| Joe    |
| Bill   |
| Bill   |
| Mike   |
| Mike   |
| Mike   |
| Mike   |
|--------|

Basically, whatever the Qty field is set to is how many times the Name is returned. I found a very similar question here, but this question is for Oracle and I'm using SQL Server. I also would prefer to do this in a SELECT statement (because it'll later be used in a view) rather than having to use temp tables or cursors.

score 7 · Accepted Answer · answered Jul 30 '14 at 17:42

7

You can use a recursive cte to do this

SQL Fiddle Example

;with cte
as
(select * from table1
union all
select name, qty - 1
from cte
where qty - 1 > 0
)

select name from cte
order by name

answered Jul 30 '14 at 17:42

SQLChao

7,709
1
17
32

score 1 · Answer 2 · answered Jul 30 '14 at 18:18

You could do this with a tally or numbers table also. The problem with a recursive cte like that is that it is really a row by row process under the hood. This approach is 100% set based.

create table #table1
(
    Name varchar(10),
    Qty int
);

insert #table1
select 'Joe', 3 union all
select 'Bill', 2 union all
select 'Mike', 4;

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    Numbers(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1
    )

select *
from #table1 t
join numbers n on n.N <= t.Qty

drop table #table1

Returning repeating number of rows in SQL

2 Answers2