Post reply

Script to produce extra rows based on number value in column

  • October 18, 2018 at 4:30 pm

    #381394

    I have a table like the data in the top circle below, and I'm trying to write a script that would produce output like the bottom circle.  Does anyone know a slick way to do this maybe with a cursor or while loop?  I can think of maybe using two look up tables and left joining to them, to produce the extra rows, but that seems like a lot of work.

    image.png

  • October 18, 2018 at 7:45 pm

    #2010344

    Please please please don't post pictures of your database. Those are worthless. I can't copy & paste into SSMS and run it.  So since I don't have real tables, I'll give a more generic answer...

    Use a Tally table...

    Say I have a table like this:
    CREATE TABLE SomeData (
        TestID INT PRIMARY KEY,
         NumberOfRepeats TINYINT
    );

    So I populate it with some data
    INSERT INTO Test (TestID, NumberOfRepeats) VALUES (1, 2), (2,4);
    Then I join it to a tally table (table of Numbers... Jeff Moden has an article on it... )

    SELECT s.TestID, s.NumberOfRepeats, t.Num
    FROM SomeData s INNER JOIN Tally t ON s.NumberOfRepeats >= t.Num;

    The resulting query will return 6 records. (2 for TestID = 1, 4 for TestID = 4).

    Hope it helps

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply