Help CONVERT Access PIVOT QUERY to SQL SERVER

[MsLady]

Can anyone help me convert this pivot query to work in sql server please?
I'll love you forever if you help me please

TRANSFORM Count(Employees.MaritalStatus) AS MaritalStatusCount
SELECT Employees.MaritalStatus
FROM Employees INNER JOIN Offices ON Employees.OfficeId = Offices.officeId
WHERE ((Not (Employees.MaritalStatus) Is Null))
GROUP BY Employees.MaritalStatus
PIVOT Offices.officeLocation;

 

[pbaldy]

I can't have you loving me forever, as it would probably put my wife into a very sour mood.

There is no direct way as the Transform/Pivot syntax isn't supported in T-SQL (2000 anyway). There's a method in the "MS Access Developer's Guide to SQL Server" by Chipman Baron (an excellent book). Their code is apparently available at the publisher's website: www.samspublishing.com. The code is in chapter 12, if that helps in the search.

 

[MsLady]

I can't have you loving me forever, as it would probably put my wife into a very sour mood.

There is no direct way as the Transform/Pivot syntax isn't supported in T-SQL (2000 anyway). There's a method in the "MS Access Developer's Guide to SQL Server" by Chipman Baron (an excellent book). Their code is apparently available at the publisher's website: www.samspublishing.com. The code is in chapter 12, if that helps in the search.

whoah! i've seen the code and i can't understand jack! about what it's doing.
i know there are different methods. but this is pretty new to me.
any help in getting my Access pivot query to work in sqlserver 2000 will be greatly appreciated please

pbaldy: thanks, i appreciate the leads. ok don't want her in sour mood, so i'd settle at "loving" her forever instead Hopefully that won't put you in a sour mood