Dynamically create new record based on the data inside a column

Question

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'

Answer 1

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.