So, we have 3 columns usually DateTime, Value and Name
Simple sql query:
select [DateTimeSaved], NValue,m.DPT_Name from NumericValues n
inner join DataPoints m on n.DataPointId=m.DataPointId
Result:
DateTimeSaved |
Nvalue |
DPT_NAME |
9/26/11 |
11.8 |
USDbuy |
2/7/12 |
15.7 |
EURbuy |
2/5/12 |
15.7 |
EURbuy |
2/3/12 |
15.7 |
EURbuy |
2/2/12 |
15.62 |
EURbuy |
2/1/12 |
15.45 |
EURbuy |
1/31/12 |
15.45 |
EURbuy |
1/30/12 |
15.4 |
EURbuy |
1/29/12 |
15.3 |
EURbuy |
1/28/12 |
15.3 |
EURbuy |
We need transform it to cross table (pivot) like this:
(no column name) |
Eurbuy |
Eursell |
9/26/11 |
15.91 |
16.11 |
9/27/11 |
16.09 |
16.35 |
9/29/11 |
16.1194 |
16.3581 |
Query:
/*if you have a lot of data, you should use temporary sql table instead of table variable */
declare @TableColumns table( DPT_NAME varchar(255) )
/*Get and insert future column names into table variable*/
insert into @TableColumns
select distinct DPT_NAME from DataPoints
declare @Columns varchar(Max)
Set @Columns =''
/* Create one string with column names separate by comma */
select @Columns=@Columns+',['+ DPT_NAME+']' from @TableColumns
Set @Columns =substring(@Columns,2,LEN(@Columns))
/*Debug message PRINT @Columns */
declare @SQL varchar(MAX)
/*Create sql query for dynamic execution*/
SET @SQL=N'Select convert(varchar, [DateTimeSaved], 101),'+@Columns+' '+
' from (' +
' select [DateTimeSaved], NValue,m.DPT_Name from NumericValues n
inner join DataPoints m on n.DataPointId=m.DataPointId '+
') as sourcetable '+
'PIVOT ( '+
' MAX([NValue]) for DPT_Name in ('+@Columns+' ) '+
') as PivotTable '
EXEC (@SQL)
/*Where Nvalue can be string or numeric, Also you can use other aggregate functions SUM, etc */