Dynamics GP basic pricing export to Excel with crosstab SQL

The following SQL table function allows exporting of GP prices into Excel.

Assumes max of 6 price breaks, but can be expanded.

Usage:

SELECT * FROM [Extract_PricesCrosstabTable_Fast] ('GBP','TRADE','%') ORDER BY ITEMNMBR

-- =============================================
-- Author:        Tim Wappat 
-- Create date: 2010-08-02
-- Description:    Function to crosstab price breaks for price level

-- Faster set based non-cursor version of crosstab for pricing
-- =============================================

CREATE FUNCTION [Extract_PricesCrosstabTable_Fast] (
     @CURNCYID VARCHAR(15)
    ,@PRCLEVEL VARCHAR(11)
    ,@PATTERN NVARCHAR(31) = '%'
    )

RETURNS @ReturnTable TABLE (
    [ITEMNMBR] [varchar](31) PRIMARY KEY NOT NULL
    ,[BREAK1] [varchar](255) NOT NULL
    ,[PRICE1] [numeric](19, 5) NULL
    ,[BREAK2] [varchar](255) NOT NULL
    ,[PRICE2] [numeric](19, 5) NULL
    ,[BREAK3] [varchar](255) NOT NULL
    ,[PRICE3] [numeric](19, 5) NULL
    ,[BREAK4] [varchar](255) NOT NULL
    ,[PRICE4] [numeric](19, 5) NULL
    ,[BREAK5] [varchar](255) NOT NULL
    ,[PRICE5] [numeric](19, 5) NULL
    ,[BREAK6] [varchar](255) NOT NULL
    ,[PRICE6] [numeric](19, 5) NULL
    )
AS
BEGIN
    
    WITH PriceTableCte (
        itemnmbr
        ,curncyid
        ,prclevel
        ,uomprice
        ,fromqty
        ,toqty
        ,[PriceBreak]
        )
    AS (
        SELECT ITEMNMBR
            ,CURNCYID
            ,PRCLEVEL
            ,UOMPRICE
            ,FROMQTY
            ,TOQTY
            ,ROW_NUMBER() OVER (
                PARTITION BY ITEMNMBR
                ,PRCLEVEL
                ,CURNCYID ORDER BY TOQTY ASC
                ) AS 'PriceBreak'
        FROM IV00108
        WHERE ITEMNMBR LIKE @PATTERN
            AND PRCLEVEL = @PRCLEVEL
            AND CURNCYID = @CURNCYID
        
        )
    INSERT @ReturnTable
    SELECT ITEMNMBR
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 1    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak1
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 1    THEN UOMPRICE
                END) AS Price1
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 2    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak2
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 2
                    THEN UOMPRICE
                END) AS Price2
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 3    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak3
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 3
                    THEN UOMPRICE
                END) AS Price3
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 4    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak4
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 4
                    THEN UOMPRICE
                END) AS Price4
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 5    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak5
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 5
                    THEN UOMPRICE
                END) AS Price5
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 6    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak6
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 6
                    THEN UOMPRICE
                END) AS Price6
    FROM PriceTableCte
    
    GROUP BY ITEMNMBR
        ,CURNCYID
        ,PRCLEVEL
    ORDER BY ITEMNMBR;

    RETURN
END;