error pivot query

Question

I would like to know if this can be done in sql server 2005 express edition.
It is a pivot query and i keep on getting this error: incorect syntax near '('
it would look like a parentesis error, but i can,t see it. Please help, Thanks!This is the code:
SELECT     room[12], [3], [18], [11], [16], [15]
FROM         (SELECT     datein, resv, room
                       FROM          Table1) PIVOT (max(resv) FOR room IN ([12], [3], [18], [11], [16], [15]))

bing siu · Answer

Thanks for your reply. But I do not understand how to to set up my query. It seems like if i have to create a new table but i dont see the select part of the query from my real table where i have the information. Please if you could explaing. Thanks

Bechir Bejaoui · Answer

This is a sample of how to use PIVOT in SQL Server : CREATE TABLE dbo.SalesByQuarter ( Y INT Q INT sales INT PRIMARY KEY (YQ) ) GO INSERT dbo.SalesByQuarter(YQSales) SELECT 2003 2 479000 UNION SELECT 2003 3 321000 UNION SELECT 2003 4 324000 UNION SELECT 2004 1 612000 UNION SELECT 2004 2 524000 UNION SELECT 2004 3 342000 UNION SELECT 2004 4 357000 UNION SELECT 2005 1 734000 GO SELECT Y [1] AS Q1 [2] AS Q2 [3] AS Q3 [4] AS Q4 FROM (SELECT Y Q Sales FROM SalesByQuarter) s PIVOT ( SUM(Sales) FOR Q IN ([1][2][3][4]) ) p ORDER BY [Y] GO DROP TABLE dbo.SalesByQuarter GO