Post reply

Change table structure

  • November 12, 2015 at 8:57 am

    #399317

    I am working with a table that does not work for me. I want to create a new table with a different structure. Here is an example of how the table is now:

    Car ColorOption1 ColorOption2 ColorOption3

    Corvette Red Black Yellow

    I want the table to be like this:

    Car ColorOption

    Corvette Red

    Corvette Black

    Corvette Yellow

    So my question is, is there a way that I can insert into the new table from the old table? A query that will restructure the data into the format of the new table so it can be inserted?

    Thanks

  • November 12, 2015 at 9:03 am

    #1839417

    robert.wiglesworth (11/12/2015)

    I am working with a table that does not work for me. I want to create a new table with a different structure. Here is an example of how the table is now:

    Car ColorOption1 ColorOption2 ColorOption3

    Corvette Red Black Yellow

    I want the table to be like this:

    Car ColorOption

    Corvette Red

    Corvette Black

    Corvette Yellow

    So my question is, is there a way that I can insert into the new table from the old table? A query that will restructure the data into the format of the new table so it can be inserted?

    Thanks

    Yes there is.

    Insert NewTab(Car, ColorOption)

    select Car, ColorOption1 from OldTab

    union all

    select Car, ColorOption2 from OldTab

    union all

    select Car, ColorOption3 from OldTab

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • November 12, 2015 at 9:11 am

    #1839420

    Well that was simple enough. Shame I didn't think of it.

    Thanks!

  • November 12, 2015 at 12:33 pm

    #1839487

    You can also do it with just a single scan of the data table:

    SELECT ca1.*

    FROM table_name

    CROSS APPLY (

    VALUES(Car, ColorOption1),(Car, ColorOption2),(Car, ColorOption3)

    ) AS ca1(Car,ColorOption)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • November 13, 2015 at 12:39 am

    #1839535

    Nice one Scott. Must remember that trick.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 5 posts - 1 through 4 (of 4 total)

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