블로그 이미지
Sunny's

calendar

« » 2025.1
일 월 화 수 목 금 토
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31

Notice

'pivot'에 해당되는 글 2건

  1. 2011.07.14 Incorrect syntax near PIVOT
  2. 2009.03.16 MSSQL 동적 PIVOT 데이터 처리
2011. 7. 14. 14:41 MSSQL

Incorrect syntax near PIVOT


Msg 325, Level 15, State 1, Line 9 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.

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.

Why I’m getting this error?
The PIVOT command is not available for SQL Server 2000. When we upgrade the SQL Server 2000 instance to SQL Server 2005, the database COMPATIBILITY_LEVEL remain in 80 until we change it manually.

Solution:

If you are running SQL 2005
EXEC sp_dbcmptlevel 'myDatabaseName', 90

If you are running SQL 2008
EXEC sp_dbcmptlevel 'myDatabaseName', 100

저작자표시
posted by Sunny's
2009. 3. 16. 13:22 MSSQL

MSSQL 동적 PIVOT 데이터 처리


-- #1. 날짜 데이터
-- 동적 날짜 데이터 생성
 WITH mycte AS
 (

     SELECT cast('2008-08-01' as datetime) DateValue
     UNION all
     SELECT DateValue + 1
     FROM mycte
     WHERE DateValue + 1 <= '2008-08-31'

 )
 SELECT Convert(char(10),DateValue,120) as Date FROM mycte
 OPTION (MAXRECURSION 0)

 

 

-- #2. 동적 PIVOT

 -- 임시 테이블 생성
 CREATE TABLE #Orders (
    Customer varchar(8),
    Product varchar(5),
    Quantity int
 )
 GO

 -- 데이터 입력
 INSERT INTO #Orders VALUES('Mike', 'Bike', 3)
 INSERT INTO #Orders VALUES('Mike', 'Chain', 2)
 INSERT INTO #Orders VALUES('Mike', 'Bike', 5)
 INSERT INTO #Orders VALUES('Lisa', 'Bike', 3)
 INSERT INTO #Orders VALUES('Lisa', 'Chain', 3)
 INSERT INTO #Orders VALUES('LIsa', 'Bike', 4)
 GO

 SELECT * FROM #Orders
 GO

 -- PIVOT 테스트
 SELECT * FROM #Orders
    PIVOT (SUM(Quantity) FOR Product IN (Bike, Chain)
 ) AS PVT
 GO

 

 -- 추가 데이터 입력
 INSERT INTO #Orders VALUES('Mike', 'Chain', 1)
 INSERT INTO #Orders VALUES('Mike', 'Light', 2)
 INSERT INTO #Orders VALUES('Lisa', 'Chain', 2)
 INSERT INTO #Orders VALUES('LIsa', 'Light', 3)
 GO

 
 -- 추가된 Product 인 Light 에 대한 조회를 할 수 없기에 동적으로 Product 를 셀렉트 할 수 있는 쿼리 생성
 DECLARE @Prod varchar(2000)
 SET @Prod = ''
 SELECT @Prod = @Prod + '[' + Product + '],'
    FROM ( SELECT Distinct Product FROM #Orders ) A

 SET @Prod = LEFT(@Prod, LEN(@Prod) - 1)

 -- 실행
 EXEC ('SELECT * FROM #Orders
   PIVOT ( SUM(Quantity) FOR Product IN (' + @Prod + ')
   ) AS PVT')
 GO

 -- 임시 테이블 삭제
 Drop Table #Orders


 

posted by Sunny's
prev 1 next

티스토리툴바