7developers blog

Useful articles, videos and code examples from professional developers

Cross table (pivot) using sql query on Microsoft SQL Server

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 */

Loading