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.
