I am trying to use the PIVOT function in SQL Server 2008. I have a query that I think should work, but whenever I execute it, I get the errors:
Msg 265, Level 16, State 1, Line 25
The column name "CompleteTime" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'BuildType'.
The query is:
; with tTemp as ( SELECT PARSENAME(CBuild,2) as BuildType, CompleteTime FROM [PerfData].[dbo].[Perf_PageChangeData] WHERE (RunId = 949 OR RunId = 966 OR RunId = 992 OR RunId=1055 OR RunId = 1093 OR RunId=1121 OR RunId=1129 OR RunId=1132 OR RunId=1142) AND (ClientModel='V8-350' OR ClientModel='V6-225') AND NewPage='FullScreen' AND PreviousPage='FullScreen' AND NewSubPage='' AND PreviousSubPage='' AND (NewLayer='' OR NewLayer='FullScreen') AND (PreviousLayer='' OR PreviousLayer='FullScreen') AND NewMenu='' AND PreviousMenu='' AND NewCH<>PreviousCH AND (KeyCode=14 OR KeyCode=15) AND (KeyName='ch_down' OR KeyName='ch_up' OR KeyName='ManualKey') AND NewType = 'SD' AND PrevType = 'SD' AND TCName = 'ChChange' ) SELECT BuildType, CompleteTime FROM tTemp PIVOT (AVG(CompleteTime) FOR BuildType IN (CompleteTime)) AS pvt
I've been working with this for quite a while now and am really pressed for time. Can anyone plesae help me out?