Unanswered: Combining 3 columns into one (not concatenation)
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
Of course I would also provide a link between the part and the applicable addons.
select Order, Part, Addon1 as Addon from [YourTable] where Addon1 is not null
select Order, Part, Addon2 as Addon from [YourTable] where Addon2 is not null
select Order, Part, Addon3 as Addon from [YourTable] where Addon3 is not null
If it's not practically useful, then it's practically useless.