error pivot query

bing siu

18y2.6k

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]))

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

bing siu18y ago
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 don,t see the select part of the query from my real table where i have the information. Please if you could explaing. Thanks
0
Bechir Bejaoui18y ago
This is a sample of how to use PIVOT in SQL Server :

CREATE TABLE dbo.SalesByQuarter
(
Y INT,
Q INT,
sales INT,
PRIMARY KEY (Y,Q)
)
GO

INSERT dbo.SalesByQuarter(Y,Q,Sales)
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
0