根据列值生成行

本文介绍了根据列值生成行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的数据库中的一个表包含带有申请编号和其他相关信息的行.我正在尝试创建第二个表(使用 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