根据列值生成行 [英] Generating Rows Based on Column Value

问题描述

我的数据库中的一个表包含带有申请编号和其他相关信息的行.我正在尝试创建第二个表(使用 INSERT INTO 语句填充)，该表复制这些行并根据 QuantityOrdered 列中的值添加一个系列值.>

例如第一个表格如下所示:

+-------------+---------+|征用 |数量 |+------------+------------+|10001_01_AD |4 |+------------+------------+

我希望输出如下:

+-------------+----------+----------+|征用 |数量 |系列 |+-------------+----------+---------+|10001_01_AD |4 |1 ||10001_01_AD |4 |2 ||10001_01_AD |4 |3 ||10001_01_AD |4 |4 |+-------------+----------+---------+

我一直在尝试使用 Row_Number() 对值进行排序，但它根据 Requisition 值的实例而不是 Quantity 值对行进行编号.

解决方案

非递归方式:

SELECT *从选项卡 t交叉申请(选择 nFROM (SELECT ROW_NUMBER() OVER(ORDER BY 1/0) AS nFROM master..spt_values s1) AS subWHERE sub.n <= t.Quantity) AS s2(Series);

db<>小提琴演示

One of my tables in my database contains rows with requisition numbers and other related info. I am trying to create a second table (populated with an INSERT INTO statement) that duplicates these rows and adds a series value based on the value in the QuantityOrdered column.

For example, the first table is shown below:

+-------------+----------+
| Requisition | Quantity |
+-------------+----------+
| 10001_01_AD |    4     |
+-------------+----------+

and I would like the output to be as follows:

+-------------+----------+----------+
| Requisition | Quantity |  Series  |
+-------------+----------+----------+
| 10001_01_AD |     4    |     1    |
| 10001_01_AD |     4    |     2    |
| 10001_01_AD |     4    |     3    |
| 10001_01_AD |     4    |     4    |
+-------------+----------+----------+

I've been attempting to use Row_Number() to sequence the values but it's numbering rows based on instances of Requisition values, not based on the Quantity value.

解决方案

Non-recursive way:

SELECT *
FROM tab t
CROSS APPLY (SELECT n
             FROM (SELECT ROW_NUMBER() OVER(ORDER BY 1/0) AS n
                   FROM master..spt_values s1) AS sub
             WHERE  sub.n <= t.Quantity) AS s2(Series);

db<>fiddle demo

这篇关于根据列值生成行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！