The following is a SAMPLE data from an excel spreadsheet. This SAMPLE data has many other fields as date. Here I have only used two date columns i.e. 28 Dec 2006 and 29 Dec 2006
This data needs to be exported into sql server 2005 table which has the fields below where I have placed the data into a table.
How can this be done please?
data:
Ref Sector Name 28 Dec 2006 29 Dec 2006
1 Sovereign RUSSIA 05 null 173.21
2 Sovereign RUSSIA 07 102.99 102.22
3 Sovereign RUSSIA 10 114.33 104.63
4 Sovereign RUSSIA 18 115.50 145.50
...
sql server table
create table tblData
(
DataID int,
Ref int,
Sector varchar(20),
Name varchar(20),
Date datetime,
value decimal(6,2)
)
DataID Ref Sector Name Date value
1 1 Sovereign RUSSIA 05 28 Dec 2006 null
2 1 Sovereign RUSSIA 05 29 Dec 2006 173.21
3 2 Sovereign RUSSIA 07 28 Dec 2006 102.99
4 2 Sovereign RUSSIA 07 29 Dec 2006 102.22
5 3 Sovereign RUSSIA 10 28 Dec 2006 114.33
6 3 Sovereign RUSSIA 10 29 Dec 2006 104.63
7 4 Sovereign RUSSIA 18 28 Dec 2006 115.50
8 4 Sovereign RUSSIA 18 29 Dec 2006 145.50
...
First import the data into temp table..
Then use the UNPIVOT operator to get the required data..
Here the complete query,
Code Snippet
/*
create table tblData
(
DataID int identity(1,1),
Ref int,
Sector varchar(20),
Name varchar(20),
Date datetime,
value decimal(6,2)
)
*/
Code Snippet
Create Table #tempdata (
[Ref] int ,
[Sector] Varchar(100) ,
[Name] Varchar(100) ,
[28-Dec-2006] float ,
[29-Dec-2006] float
);
Insert Into #tempdata Values('1','Sovereign','RUSSIA05',NULL,'173.21');
Insert Into #tempdata Values('2','Sovereign','RUSSIA07','102.99','102.22');
Insert Into #tempdata Values('3','Sovereign','RUSSIA10','114.33','104.63');
Insert Into #tempdata Values('4','Sovereign','RUSSIA18','115.50','145.50');
Go
Code Snippet
Declare @.UnPivotColumns as varchar(max)
Select@.UnPivotColumns = ''
Select @.UnPivotColumns = @.UnPivotColumns + ',[' + name + ']' from tempdb.Sys.columns
Where object_id = object_id('tempdb..#tempdata')
and column_id > 3
Select@.UnPivotColumns = Substring(@.UnPivotColumns, 2, len(@.UnPivotColumns)-1)
Insert Into tblData(ref,sector,name,date,value)
Exec ('Select ref,sector,Name,cast(date as datetime),[value]
from #tempdata unpivot([value] for [date]
in (' + @.UnPivotColumns + ') )as uptv')
Drop table #tempdata;
--To fill the missed values when the value is null
Insert Into tblData
select
fulldata.*,
tbl.value
from
(
select * from
(select distinct ref,sector,namefrom tblData) data
cross join (select distinctDatefrom tblData) dates
) fulldata
left outer join tblData tbl
on tbl.ref = fulldata.ref
and tbl.sector = fulldata.sector
and tbl.name = fulldata.name
and tbl.Date = fulldata.date
where
tbl.Date is null
Select * from tblData
