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)

    “Over the low, barnacled, elephant-colored rocks,
    Come the first tide-ripples, moving, almost without sound, toward
    me,
    Running along the narrow furrows of the shore, the rows of dead clam shells;”
    —Theodore Roethke (1908–1963)

    “Because it is not always easy for an adult to predict what inaccurate ideas a child may have, it can help to answer questions first with one of your own: “What do you think?” Once you find out what a child is really asking, you’ll be in a better position to give a helpful answer based on the facts.”
    —Joanna Cole (20th century)

    “It is easier to sail many thousand miles through cold and storm and cannibals, in a government ship, with five hundred men and boys to assist one, than it is to explore the private sea, the Atlantic and Pacific Ocean of one’s being alone.... It is not worth the while to go round the world to count the cats in Zanzibar.”
    —Henry David Thoreau (1817–1862)

    “The salt person and blasted place
    I furnish with the meat of a fable;
    If the dead starve, their stomachs turn to tumble
    An upright man in the antipodes
    Or spray-based and rock-chested sea:
    Over the past table I repeat this present grace.”
    —Dylan Thomas (1914–1953)