Daily Archives: Sep 21, 2014


Generate Defined number of rows dynamically (SET based approach)

In this article , we will generate as much number of rows as needed for each row of given table dynamically. We have a table named #Employee with two columns “EmpName” and “RowsToGenerate”. Lets explore “Generate rows dynamically” topic in with examples and demos.

Generate Rows dynamically as per given count:

Below is the structure of the table #Employee

Now put some demo values in this table as below;

Trick

I will create a CTE (Common table expression) with serial numbers to perform a join on #Employee table and this created CTE to generate the rows as much as we want as per column value “RowsToGenerate”. In case of 0, it will not generate any row.

Have a look on this CTE;

Now to get the final output as desired;

And this will generate the output in set based approach.

Conclusion:

In this article we learned, generating random number of … More