Pivot Table in SQL Server vs. Access

[kwvh]

I have been using a "Crosstab" query in Access for quite some time. Have now moved the data to SQL Server, and want to use SQL Server to create the crosstab/Pivot query. Below is my SQL Statement in SQL Server that gives me an error, followed by the SQL Statement from Access that works correctly:

Code: Select all

SELECT strUnique as RefNum ,dtmAsOf, curChangeInAllocation AS [Total ITD Allocation]
FROM 
(Select strUnique, dtmAsOf ,curChangeInAllocation
from tblCAChangeInAllocation) Chng
  
PIVOT
( 
Sum(curChangeInAllocation) 
)as pvt

Code: Select all

TRANSFORM Sum(tblChangeInAllocation.curChangeInAllocation) AS SumOfChangeInAllocation
SELECT tblChangeInAllocation.strUnique, Sum(tblChangeInAllocation.curChangeInAllocation) AS [Total ITD Allocation]
FROM tblChangeInAllocation
GROUP BY tblChangeInAllocation.strUnique
PIVOT tblChangeInAllocation.dtmAsOf;

Is there anything obviously apparent that I am missing? A closing or opening parenthesis, or a bracket or key word?

Any help is GREATLY appreciated.

Respectfully,

Ken

[HansV]

Which version of SQL Server are you using?

[kwvh]

SQL Server 2008 R2

[HansV]

OK, so pivot should be available. I can't help you with the syntax, though, I hope that someone who knows SQL Server will have a suggestion for you.

[Wendell]

To be honest, I continue to use the Access Crosstab query with SQL Server linked tables, rather than trying to sort my way though usint the PIVOT statement in SQL Server. But if that isn't an option, it appears to me that your basic syntax is correct, but the examples I see always have an IN() clause the enumerates the categories that are to be displayed as column headers. There are numerous tutorials and examples available but maybe this one will give you some further clues. It would also help to see some sample data that you are trying to analyze.

[kwvh]

Awesome Wendell! Thanks!