Cross tabbing with just APPLY

  • Hi,

    I want to create a approach for solving the cross tabbing statistics report.

    I know some method like CASE expression and PIVOT. But I want achieve it just with APPLY table operator.

    My DDL is:

    BEGIN TRAN;

    CREATE TABLE Products

    (product_name VARCHAR(15) NOT NULL

    PRIMARY KEY CLUSTERED,

    [weight] INTEGER NOT NULL,

    CHECK (weight > 0));

    GO

    INSERT INTO Products (product_name, [weight])

    VALUES ('P1', 5),

    ('P2', 25),

    ('P3', 12),

    ('P4', 1);

    GO

    CREATE TABLE Sales

    (product_name VARCHAR(15) NOT NULL

    REFERENCES Products (product_name),

    product_price DECIMAL(5,2) NOT NULL,

    qty INTEGER NOT NULL,

    sales_year INTEGER NOT NULL);

    GO

    INSERT Sales (product_name, product_price, qty, sales_year)

    VALUES('P1', 85.5, 4, 1990),

    ('P1', 100, 10, 1990),

    ('P1', 121, 7, 1991),

    ('P2', 50, 15, 1993),

    ('P2', 85.5, 13, 1994),

    ('P3', 12, 1, 1990),

    ('P3', 7, 25, 1991),

    ('P3', 7.5, 14, 1991),

    ('P3', 5, 3, 1993);

    GO

    COMMIT TRAN

    And the CASE method is:

    SELECT P1.product_name,

    year1 = SUM(CASE WHEN sales_year = 1990 THEN qty * product_price ELSE 0 END),

    year2 = SUM(CASE WHEN sales_year = 1991 THEN qty * product_price ELSE 0 END),

    year3 = SUM(CASE WHEN sales_year = 1992 THEN qty * product_price ELSE 0 END),

    year4 = SUM(CASE WHEN sales_year = 1993 THEN qty * product_price ELSE 0 END),

    year4 = SUM(CASE WHEN sales_year = 1994 THEN qty * product_price ELSE 0 END),

    totals = COALESCE(SUM(qty * product_price), 0)

    FROM Sales AS S1

    RIGHT OUTER JOIN Products AS P1

    ON S1.product_name = P1.product_name

    GROUP BY P1.product_name;

  • What column do you want to use in your table referenced in the APPLY statement?

    Either way, it most probably won't work the way you probably have in mind...

    If you want to apply the values for sales_year to populate separate columns you'd need to pivot the data since the APPLY operator will expand the rows, not columns.

    And if you want to use it for products then you'd end up with something similar to what you already have: a CASE statement.

    I'm not sure if Paul White will stumble across this post and come up with a method that will actually work, but I can't think of a simple way to do it...

    Did you read Pauls excellent article[/url] regarding CROSS APPLY (including Part II)?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • At last, I got it.

    CREATE FUNCTION Cross_tabbing (@product_name VARCHAR(15), @year INT)

    RETURNS TABLE AS

    RETURN

    (SELECT SUM(qty * product_price) AS total

    FROM Sales S

    WHERE S.product_name = @product_name

    AND S.sales_year = @year);

    GO

    SELECT P.product_name

    , D1.total AS [1990]

    , D2.total AS [1991]

    , D3.total AS [1992]

    , D4.total AS [1993]

    , D5.total AS [1994]

    , T.Total

    FROM Products P

    OUTER APPLY dbo.Cross_tabbing(P.product_name, '1990') D1

    OUTER APPLY dbo.Cross_tabbing(P.product_name, '1991') D2

    OUTER APPLY dbo.Cross_tabbing(P.product_name, '1992') D3

    OUTER APPLY dbo.Cross_tabbing(P.product_name, '1993') D4

    OUTER APPLY dbo.Cross_tabbing(P.product_name, '1994') D5

    OUTER APPLY (SELECT SUM(qty * product_price) AS Total

    FROM Sales S

    WHERE S.product_name = P.product_name) T;

  • I am sure that the "CASE" version is way much faster!


    * Noel

  • like I said:

    I can't think of a simple way to do it...

    😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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