如何编写SQL Server存储过程以获取以下输出？

I have a SourceTable like this with 2 column names:

我有一个像这样的SourceTable有两个列名:

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

The first column has some letter, and the second column carries its frequency.

第一列有一些字母,第二列有其频率。

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

我们需要编写一个存储过程并在这样的TargetTable中获取输出。

We CAN NOT use any loop, or iteration for this.

我们不能使用任何循环或迭代。

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

4 个解决方案

#1

How about a recursive CTE?

递归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;

#2

Assumes there is a low known max value for Col2 (in this case 10):

假设Col2的最大已知值较低(在本例中为10):

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

#3

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

如果您不想使用递归cte或有数字表,可以使用master..spt_values为您生成数字:

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 @t t
    inner join rn
     on(t.Col2 >= rn.rn)
order by t.Col1

#4

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

我会用一张数字表。例如,请参阅:https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

It is a table Numbers that has one column Number with values from 1 to some large enough amount. I personally have a table of numbers with 100K rows. It is possible to generate it on the fly, but it is handy in many cases, so I have a permanent table.

它是一个表格,其中有一列数字,其值从1到足够大。我个人有一个100K行的数字表。它可以在运行中生成它,但在很多情况下它很方便,所以我有一个永久表。

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

#1