Considering the below example Person table, I am looking for a way to return multiple rows based on an X value. So, if the BagsOut count is, say 3, I would want 3 copies of the row returned or if it was 2 then 2 copies should be returned, and so on:

ID      Name      BagsOut
1       Ken       1
2       Dave      3
3       Ben       2

Desired result:

ID      Name      BagsOut
1       Ken       1
2       Dave      3
2       Dave      3
2       Dave      3
3       Ben       2
3       Ben       2

Is it possible to write this into a single query? I am using T-SQL.

Many thanks in advance for looking.

CodePudding user response：

you can use recursive CTE to achieve this.

declare @t table(ID int,      Name char(5),      BagsOut int)

insert into @t values
(1       ,'Ken',       1)
,(2       ,'Dave',     3)
,(3       ,'Ben',       2)

;with cte_bags as
(
select id, name, BagsOut,1 as currentRow from @t 
union all
select id, name, BagsOut, currentRow 1 
from cte_bags
where currentRow < BagsOut)
select id, name, BagsOut from cte_bags
order by id