PIVOT in SQL Server

[CSharpUser]

Hi,
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?

Thanks!

[moone009]

PIVOT (AVG(CompleteTime) FOR BuildType IN (CompleteTime)) AS pvt
SELECT p.*
From(SELECT BuildType, CompleteTime
from tTemp) d
PIVOT (AVG(CompleteTime) FOR BuildType IN ([CompleteTime]))AS p

This should work if not send your table structure

[CSharpUser]

Moone009, thanks for your response; but, I do not understand what you are suggesting. Is your recommendation to add:
SELECT p.*
From(SELECT BuildType, CompleteTime
from tTemp) d
PIVOT (AVG(CompleteTime) FOR BuildType IN ([CompleteTime]))AS p

Below what is currently line #25 in the query I sent?

Thanks.

[BitBlt]

This is probably really late and you may have already figured this out, but here goes:

; 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, [949], [966], [992], [1055], [1093], [1121], [1129], [1132], [1142]
FROM tTemp 

PIVOT (AVG(CompleteTime) FOR runid IN ([949], [966], [992], [1055], [1093], [1121], [1129], [1132], [1142])) AS pvt

PIVOT depends on already knowing the values of the columns so it can construct the columns for the pivot. If you already know them, great. If not, you can construct your sql statement as a string and execute it. Here's a web site that explains how you can do this: http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx The explanation on that site works for SQL2005, but it also works for SQL2008.

Hope this is helpful! Good luck!