Eugen Hübner Eugen Hübner - 2 months ago 8
SQL Question

TSQL-View: How to multiplicate the rows of a table

I have a table with two columns and for example three rows as shown above. Then I want to create a view on this table.

This view should show for each line in the table as many lines as there is written in this line.

I have no idea how to solve this problem, has someone an example how to build a view with TSQL?

Example:

enter image description here

Answer

You should join this table with some table which has field with 1,2,3,4,5,6,.... numbers. For example:

SQLFiddle demo

with t0 as 
( select 1 n
  union all
  select 2 n
  union all 
  select 3 n 
 ),
tCount as
(select ROW_number() over (order by t1.n) rn
from t0,
     t0 as t1,
     t0 as t2,
     t0 as t3,
     t0 as t4
)
select t.* from t
join tCount on t.Number>=tCount.rn
order by name