How to reorganize data in a table

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.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

How to reorganize data in a table

Author

Topic

jfarr@floyd.org
Starting Member

3 Posts

Posted - 2009-04-30 : 11:11:21

I have a database where the current setup has a unique row number, and then the item_id, omni_stid, and item_name can be duplicates.

Ex.

ROW item_id omni_stid item_name
1 1002 FM4NRTH VALVE ANTI- REFLUX PREVENT
2 1002 FM4SOUTH VALVE ANTI- REFLUX PREVENT
3 1002 FMICU VALVE ANTI- REFLUX PREVENT
4 1002 FMPAL VALVE ANTI- REFLUX PREVENT

I need to create a new database where there is only one line for each Item_id, and then the omni_stid has each value seperated by a semicolon. Based on the sample data above, the line for item_id = 1002 should look like:

ROW item_id omni_stid item_name
1 1002 FM4NRTH;FM4SOUTH;FMICU;FMPAL VALVE ANTI- REFLUX PREVENT

I do not have any idea on how to transition from the current table setup to the new. Thanks in advance for any help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-30 : 13:45:08

It is a bad idea to store data like this. Why can't you store the data in the proper normalized way, which is your current setup, and then just display the data to the application in the format you want?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

jfarr@floyd.org
Starting Member

3 Posts

Posted - 2009-04-30 : 13:57:59

I did forget to include the reason for change in format. The data will remain in the original table in the correct format. The reason for creating the new table is at the request of our interface developer. He needs a table that only has one line for each item_id. This new table will only be used for his interface.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-30 : 14:15:41

Then write a stored procedure for the developer to use that formats the data the way he needs it. Don't create a new table to store the same data in a different format.

And frankly, your developer should be able to create this CSV in his code, it's a lot easier there than in SQL. Sounds like he needs this as a source for a dropdown box.

jfarr@floyd.org
Starting Member

3 Posts

Posted - 2009-04-30 : 14:25:21

I am relatively new to SQL and I voiced my concern for making the changes in a new table. Quite honestly, I do not even know where to begin to create a stored procedure for him to get the current data into the required format.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources