Use of Pivot in SQL Server

[RayMilhon]

I have a query in MS Access that uses a crosstab.  I need to be able to run that query in SQL Server.  I'm looking at using the Pivot command but not sure how to do this.

My Original query was 

transform count(b.field1) as cnt

select a.name

from database.dbo.table1 a

inner join database.dbo.table2 b on a.pk = b.fk

where a.field2 in ('11','aa','bb') and b.field3 between startdate() and enddate()

group by a.field1

Pivot b.field2

Thats from MS Access 2010  I tried using the syntax from SQL Server books online but keep getting syntax errors and I have no clue what I'm doing wrong.

The SQL server version looks like this

Select a.name

from database.dbo.table1 a

inner join database.dbo.table2 b on a.pk = b.fk

where a.field2 in ('11','aa','bb') and b.field3 between startdate() and enddate()

group by a.field1

pivot

    count(b.field1)

   for a.field4 as pvt

[#2]

but keep getting syntax errors

---

@RayMilhon , and what for an exact error message do you get; you haven't mentioned it in any way.

between startdate() and enddate()

In T-SQL there are no build-in functions like "startdate() and enddate()"; so UDF's?

[#3]

@olafhelper 

Yes those are UDF  Startdate()  returns the first of the month from 12 months ago.  so if run today it returns the 1st of March 2023  Enddate() returns the last of the previous month.  So today it would return the 29th of Feb 2024.  The function would return those date on any day the query is run during the current month.  Also I use the same function in several other queries with no issues.  I can post the content of those functions if you want it.

[#4]

I'm getting old. Startdate() and enddate() are 2 of several date functions I use in views and reports. I got them mixed up a bit.

The actual startdate and enddate functions used in the query are much simpler. Start date is as follows:

ALTER FUNCTION [dbo].[startdate]
(
)
RETURNS date
AS
BEGIN
-- Declare the return variable here
DECLARE @startdate as date

-- Add the T-SQL statements to compute the return value here
set @startdate = dateadd(yyyy,-1,getdate())

-- Return the result of the function
RETURN @startdate

END
GO

Enddate is:

ALTER FUNCTION [dbo].[enddate]
(
-- Add the parameters for the function here
)
RETURNS date
BEGIN
-- Declare the return variable here
DECLARE @enddate date

-- Add the T-SQL statements to compute the return value here
set @enddate = dateadd(d,-1,getdate())

-- Return the result of the function
RETURN @enddate

END
GO


There are multiple reports that use this rolling twelve month time period and again the functions work fine in all of them.