Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sarahmfr
Posting Yak Master
214 Posts |
 Posted - 2011-07-15 : 18:22:14
|
| I have an old database --poor design--the table A has 35 columnsI want to create a table B with two columnsthe first column of tabe B has all the column names of table Athe second column of table B has all the content-values of the corresponding columns in table Ai.etable A has the following columns(ref 1,ref 2, ref 3, summary, x,y,...)table B will have the following columns (colname,colvalue)(id,ref, 'dddd') where 'dddd is the value of ref1 for a particular rowsarah |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-15 : 20:17:13
|
It seems like what you are trying to do is create an "Entity-Attribute-Value" design. Most SQL experts and purists would recommend against that approach, for a lot of good reasons. So, unless the data in all the 35 columns is data that really belongs in two columns, and was just inserted as pivoted data by the original designer, reconsider whether you want to go with the EAV model. Nonetheless, you know your database and your business requirements better than I do, so may be what you are trying to do is the right thing to do. If that indeed is the case, I would simply use 35 insert statements to insert the data into the new table, SOMETHING like this:insert into NewTable (id, ref, data) select 1, 'ref1', ref1 from OldTable;insert into NewTable (id, ref, data) select 2, 'ref2', ref2 from OldTable;-- etc. |
 |
|
|
|
|
|
|
|
create a table columnname and columnvalue