Replace CASE with PIVOT

  • Dear SQL Experts,

    I have a lot of code that uses CASE Statements.

    I'm trying to wean myself off of them and use newer methods that have been introduced starting in SQL Server 2005.

    Could someone please help me on how to perform the following using a PIVOT Statement as opposed to the CASE Statements used in the code listed below?

    WITH CTE

    AS

    (

    SELECT AgcyType.AgencyTypeDescription AS [Marketing Group],

    MktRep.FirstName + ' ' + MktRep.LastName AS [Select RSM],

    CONVERT(VARCHAR(10), Quote.SalesTransferredTS, 120) AS [Date Issued],

    Quote.SalesNumber,

    Comm.CommissionAmount AS Commission,

    CASE FormName

    WHEN 'HO-A' THEN 1

    ELSE 0

    END AS [HO-A],

    CASE FormName

    WHEN 'HO-BT' THEN 1

    ELSE 0

    END AS [HO-BT],

    CASE FormName

    WHEN 'HO-CON-B' THEN 1

    ELSE 0

    END AS [HO-CON-B],

    CASE FormName

    WHEN 'TDP-1' THEN 1

    WHEN 'TDP-2' THEN 1

    WHEN 'TDP-3' THEN 1

    ELSE 0

    END AS [TDP-1],

    CASE FormName

    WHEN 'HO-3' THEN 1

    ELSE 0

    END AS [HO-3]

    FROMQuote AS Quote WITH (NOLOCK)

    INNER JOINAgency AS Agcy WITH (NOLOCK) ON Quote.AgencyID = Agcy.AgencyID

    INNER JOIN FormType AS FrmType ON Quote.FormTypeID = FrmType.FormTypeID

    INNER JOINAgencyType AS AgcyType WITH (NOLOCK)ON Agcy.AgencyTypeID = AgcyType.AgencyTypeID

    INNER JOINAgencyMarketingRep AS AgcyMktRep WITH (NOLOCK) ON Agcy.AgencyID = AgcyMktRep.AgencyID

    INNER JOINMarketingRep AS MktRep ON AgcyMktRep.MarketingRepID = MktRep.MarketingRepID

    INNER JOINCommission AS Comm ON Comm.QuoteID = Quote.QuoteID

    WHEREQuote.SalesTransferredTS >= CAST('01 jan' + CAST(DATEPART(year, GETDATE()) - 1 AS varchar) AS DATETIME)

    ANDComm.ArchiveFlag = 0

    ANDComm.CommissionTypeID IN('GPP','Grand Total Commission')

    ANDAgcy.AgencyID <> 1

    ANDAgcy.AgencyID NOT IN (5693,5694)

    ANDQuote.FirstName IS NOT NULL

    ANDQuote.CreateTS >= CAST('01 jan' + CAST(DATEPART(year, GETDATE()) - 1 AS varchar) AS DATETIME)

    )

    SELECT[Marketing Group],[Select RSM],

    [Date Issued],COUNT(SalesNumber) AS [No Of Sales],

    SUM(Commission) AS [Written Commission],

    ISNULL(SUM([HO-A]),0) AS [HO-A],

    ISNULL(SUM([HO-BT]),0) AS [HO-BT],

    ISNULL(SUM([HO-CON-B]),0) AS [HO-CON-B],

    ISNULL(SUM([TDP-1]),0) AS [TDP-1],

    ISNULL(SUM([HO-3]),0) AS [HO-3]

    FROM CTE

    GROUP BY [Marketing Group],[Select RSM],

    [Date Issued]

    ORDER BY [Date Issued] DESC

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • New does not necessarily mean better. (In fact with MS it often means buggy.) Jeff Moden shows in his article

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url] that the crosstab outperforms the pivot in most cases. He also mentions that it's much easier to do multiple aggregates with a crosstab than with a pivot.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanks for the article.:cool:

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply