CODEWITHSUNDEEP
sqlsql-server
Hirasawa Yui
Hirasawa Yui

Reputation: 1296

Duplicate rows in SELECT statement

I'm trying my best to avoid using cursors- the amount of data is really huge. There is a table that looks like:

|Data| Multiplier|
-----------------|
|A   | 2         |
|B   | 3         |
|C   | 0         |

I need to get data from it in the following way:

|Data| Multiplier|
-----------------|
|A   | 2         |
|A   | 2         |
|B   | 3         |
|B   | 3         |
|B   | 3         |

So that the row appears as much times, as it's "Multiplier" value. I know it's possible to use CONNECT statements in Oracle Database, but I need to do it in MSSQL.

Upvotes: 4

Views: 334

Answers (4)

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

You can also use cross apply

declare  @t table
(data1 varchar(50),mutli int)

insert into @t

values

('a',2),
('b',3),
('c',0),
('d',5)


select data1,mutli from @t a

cross apply (
select * from (
select ROW_NUMBER() over(order by (select null)) as rn from master..spt_values
)x where a.mutli >= rn
)z

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need recursive way :

with t as (
     select data, Multiplier, 1 as seq
     from table
     where Multiplier > 0
     union all
     select data, Multiplier, seq+1
     from t
     where seq < Multiplier
)
select *
from t
option (maxrecursion 0);

Upvotes: 5

paparazzo
paparazzo

Reputation: 45096

You have to hard code the values but pretty clean 

declare @t table (data char, multi int);
insert into @t values 
('A', 2),
('B', 3),
('C', 0);
select t.*
from @t t 
join (values (1), (2), (3), (4), (5)) v(valueId) 
on v.valueId <= t.multi

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I would just use a recursive CTE, if the numbers are not very big:

with cte as (
      select data, 1 as n, multiplier
      from t
      where multiplier > 0
      union all
      select data 1 + n, multiplier
      from cte
      where n < multiplier
    )
select data, multiplier
from cte;

There are other methods, using numbers as well:

select t.data, t.multiplier
from t join
     (select row_number() over (order by (select null)) as n
      from master..spt_values v
     ) n
     on n.n <= t.multiplier;

Upvotes: 4

Related Questions