April 27, 2010 at 6:58 am
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;
April 27, 2010 at 12:38 pm
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)?
April 27, 2010 at 12:48 pm
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;
April 27, 2010 at 1:11 pm
I am sure that the "CASE" version is way much faster!
* Noel
April 27, 2010 at 1:21 pm
like I said:
I can't think of a simple way to do it...
😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply