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)

    “A woman does not have to make decisions based on the need to survive. She can cut through issues, call shots as she sees them.... Many bad decisions are made by men in government because it is good for them personally to make bad public decisions.”
    —Dianne Feinstein (b. 1933)

    “The opera isn’t over till the fat lady sings.”
    —Anonymous.

    A modern proverb along the lines of “don’t count your chickens before they’re hatched.” This form of words has no precise origin, though both Bartlett’s Familiar Quotations (16th ed., 1992)

    “A child who is not rigorously instructed in the matter of table manners is a child whose future is being dealt with cavalierly. A person who makes an admiral’s hat out of linen napkins is not going to be in wild social demand.”
    —Fran Lebowitz (20th century)