Duplicate rows in SELECT statement

  • A+
Category:Languages

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.

 


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; 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: