How can I write a SQL Server stored procedure to get the following output?

Question

I have a SourceTable like this, with two column names:

  Col 1 | Col 2 ------------------ A | 2 B | 3 C | 4 D | 2 E | 1 F | 0 

The first column has a letter, and the second column has its own frequency.

We need to write a stored procedure and get the output in a TargetTable like this.

We CANNOT use any loop or iteration to do this.

 Col 1 ----- A A B B B C C C CD D E 

Answer 1

What about recursive CTE?

 with x as ( select col1, 1 as i, col2 as lim from t where col2 > 0 union all select col1, i + 1, lim from x where i + 1 <= lim ) select col1 from x order by col1; 

Answer 2

Assumes that for Col2 (in this case 10) there is the smallest known maximum value:

  select Col1 from tbl inner join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) T(n) on Tn <= tbl.Col2 

Answer 3

I would use a table of numbers. For example, see https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

This is the Numbers table, which has one Number column with values ​​from 1 to some fairly large amount. I personally have a table of numbers with 100 thousand lines. You can generate it on the fly , but it is very convenient in many cases, so I have a permanent table.

 SELECT A.Col1 FROM SourceTable CROSS APPLY ( SELECT SourceTable.Col1 FROM Numbers WHERE Numbers.Number <= SourceTable.Col2 ) AS A ORDER BY Col1; 

Answer 4

If you do not want to use recursive cte or have a table of numbers, you can use master..spt_values to generate your numbers for you:

 declare @t table(Col1 nvarchar(1), Col2 int); insert into @t values ('A',2),('B',3),('C',4),('D',2),('E',1),('F',0); with rn as ( -- This sub select simply makes sure you only get the maximum number required. select top (select max(Col2) from @t) row_number() over (order by (select null)) as rn from master..spt_values t1 cross join master..spt_values t2 ) select t.Col1 from @tt inner join rn on(t.Col2 >= rn.rn) order by t.Col1