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)

    “This is a war universe. War all the time. That is its nature. There may be other universes based on all sorts of other principles, but ours seems to be based on war and games.”
    —William Burroughs (b. 1914)

    “King Kong, Count Dracula, and the Phantom of the Opera are just looking for love, like the rest of us.”
    —Mason Cooley (b. 1927)

    “Life is a thin narrowness of taken-for-granted, a plank over a canyon in a fog. There is something under our feet, the taken-for-granted. A table is a table, food is food, we are we—because we don’t question these things. And science is the enemy because it is the questioner. Faith saves our souls alive by giving us a universe of the taken-for-granted.”
    —Rose Wilder Lane (1886–1968)