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


CREATE TABLE #Employee

(

EmpName VARCHAR(100),

RowsToGenerate INT

)

Now put some demo values in this table as below;


INSERT INTO #Employee(EmpName, RowsToGenerate)

VALUES('Emp1', 5)

,('Emp2', 10)

,('Emp3', 4)

,('Emp4', 1)

,('Emp5', 2)

,('Emp6', 8)

,('Emp7', 1)

,('Emp8', 0)

,('Emp9', 0)

,('Emp10', 0)

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;


;WITH CTE AS

(

SELECT 1 AS SEQ

UNION ALL

SELECT SEQ + 1 FROM CTE WHERE SEQ < 1000

)

Now to get the final output as desired;


;WITH CTE AS

(

SELECT 1 AS SEQ

UNION ALL

SELECT SEQ + 1 FROM CTE WHERE SEQ < 1000

)

SELECT * FROM #Employee

INNER JOIN CTE ON CTE.SEQ <= #Employee.RowsToGenerate

ORDER BY EmpName

OPTION(MAXRECURSION 10000)

And this will generate the output in set based approach.

Conclusion:

In this article we learned, generating random number of rows dynamically in as set based approach. We can achieve the same in another way also. But always try to achieve these types of outputs in a SET based approach, instead of using loops and cursors.

Share you feedback below.

Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.

Leave a comment

Your email address will not be published. Required fields are marked *

*