Cross tabbing with APPLY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cross tabbing with APPLY

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-27 : 07:15:12

Hi,
I want to achieve cross tabbing with APPLY. Please post the approach.

Query with CASE method:

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 07:48:47

something like:-


DECLARE @products table
(ID int identity(1,1),
product_name varchar(100),
product_price int
)
INSERT @products
VALUES ('test1',100),
('test2',200)

DECLARE @sales table
(ID int identity(1,1),
product_name varchar(100),
sales_year int,
qty int
)
INSERT @sales
VALUES ('test1',1990,10),
('test1',1994,30),
('test2',1993,20),
('test1',1991,60),
('test2',1991,100),
('test2',1994,30),
('test1',1992,50)

SELECT  Product_Name,[1990],[1991],[1992],[1993],[1994]
FROM
(
SELECT P1.Product_Name,
sales_year,product_price * total_qty AS total_price
FROM @products P1
CROSS APPLY (SELECT sales_year, SUM(qty) AS total_qty
             FROM @sales
             WHERE product_name = P1.product_name
             GROUP BY sales_year
            )S1
)m
PIVOT ( SUM(total_price) FOR sales_year IN ([1990],[1991],[1992],[1993],[1994]))p
            

output
--------------------------------
Product_Name	1990	1991	1992	1993	1994
test1	1000	6000	5000	NULL	3000
test2	NULL	20000	NULL	4000	6000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-27 : 08:03:19

Thanks. But I mean creating solution only with APPLY and not using PIVOT.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 08:05:16

any ways you need PIVOt or GROUP BY as you need aggregation operation for cross tabbing unless you use dynamic sql.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-27 : 08:31:01

okay, now I post the real DDL.
And the following I put a method that calculate cross tabbing with only APPLY (OUTER APPLY). please see and check the method that it is correct or not.

The DDLs

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

Hers is the method

SELECT P.product_name, D1.[1990], D2.[1991], D3.[1992], D4.[1993], D5.[1994], T.Total
  FROM Products P
        OUTER APPLY (SELECT SUM(qty * product_price) AS [1990]
                      FROM Sales S
                     WHERE S.product_name = P.product_name
                       AND S.sales_year = 1990) D1
	OUTER APPLY (SELECT SUM(qty * product_price) AS [1991]
                      FROM Sales S
                     WHERE S.product_name = P.product_name
                       AND S.sales_year = 1991) D2
	OUTER APPLY (SELECT SUM(qty * product_price) AS [1992]
                      FROM Sales S
                     WHERE S.product_name = P.product_name
                       AND S.sales_year = 1992) D3
	OUTER APPLY (SELECT SUM(qty * product_price) AS [1993]
                      FROM Sales S
                     WHERE S.product_name = P.product_name
                       AND S.sales_year = 1993) D4
	OUTER APPLY (SELECT SUM(qty * product_price) AS [1994]
                      FROM Sales S
                     WHERE S.product_name = P.product_name
                       AND S.sales_year = 1994) D5
        OUTER APPLY (SELECT SUM(qty * product_price) AS Total
                       FROM Sales S
                      WHERE S.product_name = P.product_name) T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 08:50:29

Is there any reason that you dont want to use PIVOT?

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-27 : 08:53:21

I want to know and familiar with all of methods for solving this problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 10:45:48

quote:
Originally posted by ms65g

I want to know and familiar with all of methods for solving this problem.

the method looks fine though i'm not sure how this would perform. depending on number of values to pivot you need to use that many number of APPLY operators

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-27 : 12:27:42

If we create a MS T-SQL inline table-valued UDF for the method then work with query will be very easy.


CREATE FUNCTION dbo.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]
		, D1.total AS [1990]
		, D1.total AS [1990]
		, D1.total AS [1990]
		, D1.total AS [1990]
		, 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 12:31:31

quote:

Originally posted by ms65g

If we create a MS T-SQL inline table-valued UDF for the method then work with query will be very easy.


CREATE FUNCTION dbo.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]
		, D1.total AS [1990]
		, D1.total AS [1990]
		, D1.total AS [1990]
		, D1.total AS [1990]
		, 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 would still prefer using the PIVOT operator though as you're still hardcoding values to be pivoted. At least it saves repetative calls to udf

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-29 : 04:55:01

Now how do you add the column that hold the total sales of each product?
Note that may be a sales_year out of the 1990-1994 years.
It means the result of [1990]+[1991]+...+[1994] wont work.

the query is like this:

  SELECT P.product_name,
         Pvt.[1990],
         Pvt.[1991],
         Pvt.[1992],
         Pvt.[1993],
         Pvt.[1994]
         --I need to show the totals here
  FROM (SELECT product_name, --groups
               sales_year,
               qty * product_price AS total
          FROM sales) D
		 PIVOT (SUM(total) 
			    FOR sales_year  
                 IN ([1990], [1991], [1992], [1993], [1994]) 
                ) AS Pvt
         RIGHT OUTER JOIN Products P
            ON P.product_name = Pvt.product_name;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-30 : 02:18:19

but even then wont this work?

ISNULL(Pvt.[1990],0) + ISNULL(Pvt.[1991],0) + ISNULL(Pvt.[1992],0) + ISNULL(Pvt.[1993],0)+ ISNULL( Pvt.[1994],0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-30 : 12:02:47

No.
I need total of all years not only 1990-1994 years.

Is this method appropriate?


   SELECT P.product_name,
         Pvt.[1990],
         Pvt.[1991],
         Pvt.[1992],
         Pvt.[1993],
         Pvt.[1994],
         D.totals
  FROM (SELECT product_name, --groups
               sales_year,
               qty * product_price AS total
          FROM sales) D
         PIVOT (SUM(total) --The aggregate that will apply to the values in the group
                FOR sales_year --The source column holding the target column names 
                 IN ([1990], [1991], [1992], [1993], [1994]) --The list of actual target column names 
                ) AS Pvt
         RIGHT OUTER JOIN Products P
            ON P.product_name = Pvt.product_name
         CROSS APPLY (SELECT SUM(qty * product_price) AS totals
                        FROM Sales
                       WHERE product_name = P.product_name) D;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 00:43:53

ok you mean entire years? then this would suffice

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources