SQL Problems Requiring Cursors - Example: Insert Rows Based On A Count in The Table Itself

Example: Insert Rows Based On A Count in The Table Itself

The table below represents types of marbles. The four text columns represent four marble characteristics. Each characteristic has two values for a total of 16 types of marbles.

The "quantity" column represents how many marbles of that type we have. The task is to create a second table holding one row for each marble of that type. Thus, the target table would have the four text columns, and a total of 40 + 20 + 20 + 10 + ... + 10 + 5 = 270 rows.

Source table:

QUANTITY TEXTURE APPEARANCE SHAPE COLOR ---------- ---------- ---------- ---------- ----- 40 smooth shiny round blue 20 smooth shiny warped blue 20 smooth dull round blue 10 smooth dull warped blue 20 rough shiny round blue 10 rough shiny warped blue 10 rough dull round blue 5 rough dull warped blue 40 rough dull warped red 20 rough dull round red 20 rough shiny warped red 10 rough shiny round red 20 smooth dull warped red 10 smooth dull round red 10 smooth shiny warped red 5 smooth shiny round red

Table to generate:

TEXTURE APPEARANCE SHAPE COLOR ---------- ---------- ---------- ----- smooth shiny round blue -- 1 smooth shiny round blue -- 2 ... -- and so on smooth shiny round blue -- 40 smooth shiny warped blue -- 1 smooth shiny warped blue -- 2 ... -- and so on smooth shiny warped blue -- 20 ... -- and so on smooth shiny round red -- 1 smooth shiny round red -- 2 smooth shiny round red -- 3 smooth shiny round red -- 4 smooth shiny round red -- 5

Read more about this topic:  SQL Problems Requiring Cursors

Famous quotes containing the words insert, rows, based, count and/or table:

    “all the fine
    Points of diving feet together toes pointed hands shaped right
    To insert her into water like a needle”
    —James Dickey (b. 1923)

    “Creatures that hang themselves up like an old rag, to sleep;
    And disgustingly upside down.
    Hanging upside down like rows of disgusting old rags
    And grinning in their sleep.
    Bats!”
    —D.H. (David Herbert)

    “Our children evaluate themselves based on the opinions we have of them. When we use harsh words, biting comments, and a sarcastic tone of voice, we plant the seeds of self-doubt in their developing minds.... Children who receive a steady diet of these types of messages end up feeling powerless, inadequate, and unimportant. They start to believe that they are bad, and that they can never do enough.”
    —Stephanie Martson (20th century)

    “The greatest waste of time he knew of was to count the hours—what good can come of it?—and the greatest illusion in the world, to lead one’s day by the sound of the clock, and not by precepts of common sense and understanding.”
    —François Rabelais (1494–1553)

    “They were not on the table with their elbows.
    They were not sleeping in the shelves of bunks.
    I saw no men there and no bones of men there.”
    —Robert Frost (1874–1963)