Forum Discussion

RayMilhon's avatar
RayMilhon
Copper Contributor
Mar 12, 2024

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

  • olafhelper's avatar
    olafhelper
    Bronze Contributor

    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?

     

    • RayMilhon's avatar
      RayMilhon
      Copper Contributor

       

      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.

      • RayMilhon's avatar
        RayMilhon
        Copper Contributor
        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.

Resources