>Hi gang. Would you test this also and confirm or say where I'm going wrong. Issue is related with this topic on MS forums:
>http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=888543&SiteID=1&mode=1
>
>Got no reply there and trying my chance here.
>
>

--Run in SSMS against AdventureWorks sample database
>SELECT TerritoryId,
>[2003-01],[2003-02],[2003-03],[2003-04],[2003-05],[2003-06],
>[2003-07],[2003-08],[2003-09],[2003-10],[2003-11],[2003-12],
>[2004-01],[2004-02],[2004-03],[2004-04],[2004-05],[2004-06],
>[2004-07]
>from ( select TerritoryId,
>   CONVERT(CHAR(7), h.OrderDate, 120) AS theDate, d.LineTotal
>   FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
>) p
>pivot (
>sum(LineTotal) for theDate in ([2003-01],[2003-02],[2003-03],[2003-04],[2003-05],[2003-06],
>[2003-07],[2003-08],[2003-09],[2003-10],[2003-11],[2003-12],
>[2004-01],[2004-02],[2004-03],[2004-04],[2004-05],[2004-06],[2004-07])
>) pvt
>order by TerritoryId
>

>
>Inner select runs in 390 milliseconds. However full SQL with pivot takes 5187 milliseconds. Repeated runs might change it a few milliseconds but the general timing stands.
>
>Now if I instead simply ask for the inner SQL from VFP and pivot myself then tmings are:
>
>For same period range used in sample (less months):
>Got data-elapsed:457
>XTabbed-total elapsed:729
>
>For all 37 months:
>Got data-elapsed:455
>XTabbed-total elapsed:741
>
>Question is where do I go wrong in PIVOT syntax?
>
>PS: Creating a CLR procedure to generate month column names insetad of hard coding, and limiting the period of inner select didn't help but even if it did, is not the main question here.
>I used my own xtabbing routine not VFP _xtab. If you need it I can provide. TIA
>Cetin

Remembered to update this one. Umachandar Jayachandran from MSSQL performance team confirmed that PIVOT provides only syntax simplification. Here is his answer as is:

"Yes, currently PIVOT only provides syntax level simplification. It does pretty much the same as hand coded SQL statement. However, this may change in a future version of SQL Server. So if you have a situation where you can use PIVOT then use it. Currently, you cannot pivot on multiple expressions so for that you can go the traditional SQL route."

Here is T-SQL with timings almost same as VFP's (VFP is still faster about 100-120 milliseconds - including building all the columns instead of hard coding like here:)

with myPivot as
( select territoryID, theDate, sum(LineTotal) as LineTotal
    from ( 
    select TerritoryID,
     CONVERT(CHAR(7), h.OrderDate, 120) AS theDate, d.LineTotal
     FROM Sales.SalesOrderHeader h 
      JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
    ) p
  group by TerritoryID, theDate
)
SELECT TerritoryID,
[2003-01],[2003-02],[2003-03],[2003-04],[2003-05],[2003-06],
[2003-07],[2003-08],[2003-09],[2003-10],[2003-11],[2003-12],
[2004-01],[2004-02],[2004-03],[2004-04],[2004-05],[2004-06],
[2004-07]
from myPivot
pivot (
   sum(LineTotal) for theDate in 
  ([2003-01],[2003-02],[2003-03],[2003-04],[2003-05],[2003-06],
   [2003-07],[2003-08],[2003-09],[2003-10],[2003-11],[2003-12],
   [2004-01],[2004-02],[2004-03],[2004-04],[2004-05],[2004-06],[2004-07])
) pvt
order by TerritoryID;

Cetin