Misc: Generate duplicate rows based on quantity - CTE

Ques: You have below records:

item_name     item_qty

Keyboard          3

Mouse             5

You need to duplicate each records depending upon the value of item_qty values.

Sol:

This can be done using CTE

;With DupCreationCTE(item_name, item_qty, item_counter) as

(

  select item_name, item_qty, 1 as item_counter from DupCreation

  union All

  select item_name, item_qty, item_counter + 1 as item_counter

  from DupCreationCTE where item_counter < item_qty

)

SELECT * from DupCreationCTE order by item_name, item_counter asc

Output:

item_name item_qty item_counter

"Keyboard" 3                 1

"Keyboard" 3                 2

"Keyboard" 3                 3

"Mouse"         5                 1

"Mouse"         5                 2

"Mouse"         5                 3

"Mouse"         5                 4

"Mouse"         5                 5

Post Reference: Vikram Aristocratic Elfin Share