PIVOT works fine for MsAccess - Now I am converting to MSSql - How please?


this PIVOT works fine for MsAccess - Now I am converting to MSSql - How would I do this  please?

TRANSFORM Sum(AceData.InCredit) AS SumOfInCredit
SELECT AceData.Type
FROM ListJob INNER JOIN AceData ON ListJob.JobList = AceData.Job
WHERE (((ListJob.ScenarioAccountant)=True))
GROUP BY AceData.Type
PIVOT AceData.Job;
goodkAsked:
Who is Participating?
 
borkiConnect With a Mentor Commented:
Which version of SQL Server? Pivot is one of the areas that have been improved in the last few versions of MS SQL.

Here is one method that I have used with SQL 2000:
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

In SQL 2005 and beyond you have the PIVOT statement, which is nowhere near as convenient as the TRANSFORM in Access. There are some good articles out there, start with:
http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx

Good luck
0
 
goodkAuthor Commented:
sql 2008

Microsoft SQL Server Management Studio                                    10.0.2531.0
Microsoft Data Access Components (MDAC)                                    6.0.6002.18005
Microsoft MSXML                                    3.0 4.0 5.0 6.0
Microsoft Internet Explorer                                    8.0.6001.19019
Microsoft .NET Framework                                    2.0.50727.4206
Operating System                                    6.0.6002
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Just so we are clear, can you post exactly what happens if you run the SQL you posted in SQL Server?
Also state the version of SQL Server you are using.

See here:
http://msdn.microsoft.com/en-us/library/ms177410.aspx

Or see this utility:
http://www.rac4sql.net/


JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
<Also state the version of SQL Server you are using.>
...never mind, you already did that...

;-)
0
 
goodkAuthor Commented:
partial
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.