MS SQL Server PivotTable with a Subquery in a Column Article

Question

I am sure this is a simple method, although I still can not find the answer!

I have

TIMESTAMP | POINTNAME | VALUE 2012-10-10 16:00:00 AHU01 20 2012-10-10 16:00:00 AHU02 25 2012-10-10 16:00:15 AHU01 26 2012-10-10 16:00:15 AHU02 35 

etc. (approximately 800 POINTNAMES)

with many pointnames I don’t want to list each of them in the “IN” clause of the “FOR” hinge (as indicated below), but I would like to use a possible subquery.

So, all I need is all the POINTNAME values ​​in the columns with TIMESTAMP and VALUE columns, so I get one TIMESTAMP value and many columns with each POINTNAME, there is only one value for POINTNAME PER TIMESTAMP, so I don’t need something fill in, so just select max anyway?

Something like:

 SELECT [TIMESTAMP] FROM ( SELECT * FROM POINT_TABLE) PIVOT( Max[Value] FOR [POINTNAME] IN (SELECT DISTINCT [POINTNAME] FROM POINT_TABLE) 

will create -

  TIMESTAMP AHU01 AHU02 2012-10-10 16:00:00 20 25 2012-10-10 16:15:00 26 35 

I understand that this is probably not easy, but I hope you get what I'm trying to achieve?

PIVOT SYNTAX:

 SELECT <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column name>, ... [last pivoted column] AS <column name> FROM (<SELECT query that produces the data>) AS <alias for the source query> PIVOT ( <aggregation function>(<column being aggregated>) FOR [<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ) AS <alias for the pivot table> <optional ORDER BY clause>; 

Answer 1

for dynamic number of columns you need to use dynamic SQL

 declare @cols nvarchar(max), @stmt nvarchar(max) select @cols = isnull(@cols + ', ', '') + '[' + T.POINTNAME + ']' from (select distinct POINTNAME from TABLE1) as T select @stmt = ' select * from TABLE1 as T pivot ( max(T.VALUE) for T.POINTNAME in (' + @cols + ') ) as P' exec sp_executesql @stmt = @stmt 

SQL FIDDLE EXAMPLE