split a single row into multiple rows in database

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 2000 Forums

Import/Export (DTS) and Replication (2000)

split a single row into multiple rows in database

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2007-05-15 : 09:47:27

I want to import an excel spreadsheet into database.
excel has following file format:
------------------------------------
Id col1 col2 col3 col4
1 20 21 22 23
2 30 31 32 33

The above records have to be split into multiple records like shown below. The database table has following structure:
--------------------------------------
Id col
1 20
1 21
1 22
1 23
2 30
2 31
2 32
2 33

How can i do this.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-15 : 10:13:43

First load you file into a temporary table in SQL Server. Then you can use the following query to load it to your final table:

SELECT [ID], [Col1] AS [Col] FROM YourTempTable
UNION ALL
SELECT [ID], [Col2] AS [Col] FROM YourTempTable
UNION ALL
SELECT [ID], [Col3] AS [Col] FROM YourTempTable
UNION ALL
SELECT [ID], [Col4] AS [Col] FROM YourTempTable

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources