Eugen Hübner Eugen Hübner - 1 year ago 79
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?


enter image description here

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download