When you import an Excel table into SQL Server you wind up with a table with all the columns from the Excel table as fields and all the rows as records. However, in a RDBMS you normally want a table with three fields: row, col, and value. To do the conversion, I created a stored procedure without any cursors.
To start, let's say you have a "testtable" imported from Excel and an empty table "test_res" with three fields: name, date, value(bedrag). The Excel data looks like this:
name [01/01/2000] [01/02/2000] aaa 10 20 bbb 30 40
Here's the stored procedure:
CREATE PROCEDURE [dbo].[transform] AS declare @t_id int declare @i int declare @sql nvarchar(500) declare @naam nvarchar(500) declare @datum datetime SET NOCOUNT OFF create table #col ( id int identity, datum datetime, datchar nvarchar(40) ) create table #col2 ( id int identity, naam nvarchar(40) ) select @t_id=id from sysobjects where name = 'testtable' insert into #col (datum,datchar) select convert(datetime,substring(name,2,len(name)-2)),name from syscolumns where id = @t_id and colid > 1 order by colid insert into #col2 (naam) select naam from testtable truncate table test_res insert into test_res (naam,datum,bedrag) select #col2.naam,#col.datum,0 from #col cross join #col2 set @i = 2 while @i < ((select count(*) from #col ) + 2) begin set @sql = N'update test_res set bedrag = [' + COL_NAME(OBJECT_ID('testtable'), @i) + '] from test_res inner join testtable on test_res.naam = testtable.naam inner join #col on #col.datum= test_res.datum and #col.id = ' + CONVERT(char(5),@i - 1) execute sp_executesql @sql select @i = @i +1 end select * from test_res
For More Information
- What do you think about this tip? E-mail the Editor at firstname.lastname@example.org with your feedback.
- The Best SQL Server Web Links: tips, tutorials, scripts, and more.
- Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.