将每个值重复n次作为SQL中的行 [英] Repeat each value n times as rows in SQL

问题描述

一段时间以来，我一直试图在SQL(Oracle 11g)中实现这一目标，但找不到合适的方法来实现.

I have been trying to achieve this in SQL (Oracle 11g) for a while but could not find a proper way to do it.

我的表names具有以下行:

NAME      REPEAT
----      ------
KAUSHIK   2
KARTHIK   3
NIDHI     1
ASHWINI   5
JAGADEESH 6

我需要的是这样的输出:

What I need is an output like this:

NAME
----
KAUSHIK    -- 2 rows
KAUSHIK
KARTHIK
KARTHIK    -- 3 rows
KARTHIK
NIDHI      -- 1 row
ASHWINI
ASHWINI    -- 5 rows
...

以此类推.

到目前为止，我尝试过的其中一个查询当然是行不通的.我也尝试使用unpivot，但似乎找不到合适的方法来完成此操作.

One of the queries which I have tried so far, which of course is not working. I tried to use unpivot as well but could not seem to find a proper way to accomplish this.

SELECT m.name
FROM names m
INNER JOIN
  ( SELECT name, repeat FROM names
  ) n
ON m.name = n.name
  CONNECT BY LEVEL <= n.repeat;

推荐答案

尝试一下:

select * from names
cross join (select rownum n from dual
            connect by level <= (select max(repeat) from names))
where n <= repeat
order by name

这篇关于将每个值重复n次作为SQL中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！