SQL Server 2005 Rows to Columns

Question

Possible Duplicate:
T-SQL Group Rows Into Columns

I have a table _data that is formatted like this

TimeStamp                        MeterID      Value
2012-04-15 13:00:00.000          CK1992       152.64
2012-04-15 12:45:00.000          CK1992       151.695
2012-07-06 12:45:00.000          CK1992       150.84
2012-09-04 12:00:00.000          CK1992       150.66
2012-04-15 12:15:00.000          CK1992       150.57

I need to be able to format that table as something like this

Timestamp     MeterID1     MeterID2     MeterID3
stamphere     value        value        value

I have a lot of different MeterIDs so it needs extract it accordingly.

Here is my existing select statement:

SELECT [TimeStamp]
  ,[MeterID]
  ,[Value]
FROM [dbo].[_Data]

How can I make this work? Thanks for your help!

it is not exactly clear what value you want in each column. Can you edit the result to with the sample data that you provided? — Taryn, Oct 12 '12 at 16:06
I cannot use PIVOT as it is SQL Server 2005. I want the column value as the value — kevin c, Oct 12 '12 at 16:12
@bluefeet Incorrect syntax near 'pivot'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel. — kevin c, Oct 12 '12 at 16:14
@kevinc if you cannot use `PIVOT`, then this can be done with a `CASE` and an aggregate function. — Taryn, Oct 12 '12 at 18:58

score 1 · Accepted Answer · edited May 23 '17 at 10:24

You might check the information in SQL Server dynamic PIVOT query? since you don't know how many columns you will have.

Also, you can potentially use PIVOT as it was introduced in SQL Server 2005:

When PIVOT and UNPIVOT are used against databases that are upgraded to SQL Server 2005 or later, the compatibility level of the database must be set to 90 or higher. For information about how to set the database compatibility level, see sp_dbcmptlevel (Transact-SQL) - MSDN

SQL Server 2005 Rows to Columns

1 Answers1