May 10, 2012 at 11:22 am
I'm using SQL Server Management Studio 2005. I was experimenting with the idea of using UNPIVOT, but when I went to use it, I get the error:
Msg 170, Level 15, State1, Line 18
Line 18: Incorrect syntax near 'UNPIVOT'
So, obviously it's not a recognized function...but I don't understand why. I have older databases, but the server I'm connected to is SQL Server 8.0.2282. Any ideas?
Here's the code:
-- Create Sample Data using a Table Varable
Create Table #Orders
(orderid int, GiftCard int, TShirt int, Shipping int)
-- Load Sample Data
INSERT INTO #Orders VALUES (1, 2, NULL, 1)
INSERT INTO #Orders VALUES (2, 2, 2, 2)
select * from #orders
--Query to Retrieve Desired Data
SELECT OrderID, convert(varchar(15), ProductName) [ProductName], ProductQty
FROM
(SELECT OrderID, GiftCard, TShirt, Shipping
FROM #Orders) p
UNPIVOT
(ProductQty FOR ProductName IN
([GiftCard], [TShirt], [Shipping])
) as unpvt
May 10, 2012 at 11:30 am
Anything with a 8.0 version on it is based on SQL 2000. So either it IS a SQL 2000 server, or it's a DB running in 2000-compatibility mode.
This unfortunately means that PIVOT/UNPIVOT will not be available.
From Books online:
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).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 10, 2012 at 11:57 am
Ah, that clears things up (unfortunately).
Thanks for the info.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply