Recursive CTE

• info 58414

  Hall of Fame

  Points: 3386

  More actions

  November 23, 2016 at 9:32 am

  #315192

  Hi,

  i have a Select with this Result;

  ID RowCount

  FKR000000000001 34

  FKR000000000002 21

  FKR000000000003 28

  Can i write a recursive Cte, which generates as many records as the RowCount indicates.

  For example: (FKR000000000001 34) = > generates 34 same Records

  Thanks

  Best Regards

  Nicole 😉

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 23, 2016 at 9:43 am

  #1914271

  info 58414 (11/23/2016)

  ---

  Hi,

  i have a Select with this Result;

  ID RowCount

  FKR000000000001 34

  FKR000000000002 21

  FKR000000000003 28

  Can i write a recursive Cte, which generates as many records as the RowCount indicates.

  For example: (FKR000000000001 34) = > generates 34 same Records

  Thanks

  Best Regards

  Nicole 😉

  There's no need for a recursive CTE and it's even a bad idea because of the performance issued it might have.

  Instead, you need a tally (or numbers) table. Learn more about them in this article: http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/

  Once you have a tally table, you just have to do a non-equi join on your table.

  SELECT *

  FROM MyTable mt

  JOIN Tally t ON mt.RowCount >= t.number;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2