Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Unanswered: Combining 3 columns into one (not concatenation)

    Greetings,
    I am trying to "Fix" a poorly normalized table, and I wanted some info on the best way to go about this. It is an orders table that has items associated with it, and also "add-ons" to those items in the same table, like so:

    order# Part# Addon1 Addon2 Addon3

    What I would like to do is break the addons into a new table. Is there a way using a query/view/SP to bring all the addon fields into one column to create a new table with? or would I have to create some form of append to add the additional columns one at a time. Here is an example of what I want:

    Old Table: addon1 Addon2 Addon3

    New Table:
    Addon1
    Addon2
    Addon3

    Of course I would also provide a link between the part and the applicable addons.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select Order, Part, Addon1 as Addon from [YourTable] where Addon1 is not null
    UNION
    select Order, Part, Addon2 as Addon from [YourTable] where Addon2 is not null
    UNION
    select Order, Part, Addon3 as Addon from [YourTable] where Addon3 is not null
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •