英文:
Dynamically create new record based on the data inside a column
问题
以下是翻译好的部分：
这是我的当前表格，包括responseid、variety、product、mixid和应用程序数量：
#当前表格
| response_id | Variety | Product | MixID | ApplicationID | NumberofApplication |
|---|---|---|---|---|---|
| mnmbaf | 776 | Moddus | 1 | NULL | 3 |
| mnmbaf | 776 | Cycocel | 1 | NULL | 3 |
| mnmbaf | 776 | Proline | 1 | NULL | 3 |
| mnmbaf | 776 | Firebid | 1 | NULL | 3 |
#期望的表格
| response_id | Variety | Product | MixID | ApplicationID |
|---|---|---|---|---|
| mnmbaf | 776 | Moddus | 1 | 1 |
| mnmbaf | 776 | Cycocel | 1 | 1 |
| mnmbaf | 776 | Proline | 1 | 1 |
| mnmbaf | 776 | Firebid | 1 | 1 |
| mnmbaf | 776 | Moddus | 1 | 2 |
| mnmbaf | 776 | Cycocel | 1 | 2 |
| mnmbaf | 776 | Proline | 1 | 2 |
| mnmbaf | 776 | Firebid | 1 | 2 |
| mnmbaf | 776 | Moddus | 1 | 3 |
| mnmbaf | 776 | Cycocel | 1 | 3 |
| mnmbaf | 776 | Proline | 1 | 3 |
| mnmbaf | 776 | Firebid | 1 | 3 |
我正在尝试创建一个查询，根据应用程序的数量创建新的行，如下所示：
假设有3个应用程序数量，那么将基于4个产品创建12行。
这是我目前的查询：
select ResponseID, Variety, Product, MixID, ApplicationID, NumberOfApplications
from #pattmp
where ResponseID = 'mnmbaf'
英文:
This is my current table which consist of responseid, variety, product, mixid and number of applications:
#Current table
| response_id | Variety | Product | MixID | ApplicationID | NumberofApplication |
|---|---|---|---|---|---|
| mnmbaf | 776 | Moddus | 1 | NULL | 3 |
| mnmbaf | 776 | Cycocel | 1 | NULL | 3 |
| mnmbaf | 776 | Proline | 1 | NULL | 3 |
| mnmbaf | 776 | Firebid | 1 | NULL | 3 |
#expected table
| response_id | Variety | Product | MixID | ApplicationID |
|---|---|---|---|---|
| mnmbaf | 776 | Moddus | 1 | 1 |
| mnmbaf | 776 | Cycocel | 1 | 1 |
| mnmbaf | 776 | Proline | 1 | 1 |
| mnmbaf | 776 | Firebid | 1 | 1 |
| mnmbaf | 776 | Moddus | 1 | 2 |
| mnmbaf | 776 | Cycocel | 1 | 2 |
| mnmbaf | 776 | Proline | 1 | 2 |
| mnmbaf | 776 | Firebid | 1 | 2 |
| mnmbaf | 776 | Moddus | 1 | 3 |
| mnmbaf | 776 | Cycocel | 1 | 3 |
| mnmbaf | 776 | Proline | 1 | 3 |
| mnmbaf | 776 | Firebid | 1 | 3 |
I am trying to create a query where we the table will create new rows based on the number of applications like below:
Lets say there are 3 number of applications, then there will be 12 rows created based on the 4 products.
This is my query as for now:
select ResponseID, Variety, Product, MixID, ApplicationID, NumberOfApplications
from #pattmp
where ResponseID = 'mnmbaf'
答案1
得分: 1
你可以在SQL Server 2022或Azure中使用GENERATE_SERIES函数。
SELECT
pt.ResponseID,
pt.Variety,
pt.Product,
pt.MixID,
ApplicationID = g.value
FROM #pattmp pt
CROSS APPLY GENERATE_SERIES(1, pt.NumberOfApplications) g
WHERE pt.ResponseID = 'mnmbaf';
在旧版本中，你可以用连接到数字表或者APPLY一个Number Series Function来替代它。
英文:
You can use the GENERATE_SERIES function starting from SQL Server 2022, or in Azure.
SELECT
pt.ResponseID,
pt.Variety,
pt.Product,
pt.MixID,
ApplicationID = g.value
FROM #pattmp pt
CROSS APPLY GENERATE_SERIES(1, pt.NumberOfApplications) g
WHERE pt.ResponseID = 'mnmbaf';
In older versions you can replace that with a join to a numbers table, or APPLY a Number Series Function.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论