Forum Discussion
Use of Pivot in SQL Server
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
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?
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.
- 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.
