none
Four different ways to write PIVOT query

    Question

  • Hi People,

    I've created during one training 4 different query producing same result

    Now I'm in trouble - I could't decide - what is tha best of those. Looks like 3rd and 4th are equal (by execution plan) - othar have "minor" differences. All they work on plain old Northwind database

    Any comments or explanations are welcome :)

    Thanks in advance

    Henn Sarv

    -- Variant 1
    select
    CategoryName, [1996], [1997], [1998]
    from (
    	select 
    	c.CategoryName
    	, YEAR(o.orderdate) OrderYear
    	, d.Quantity * d.UnitPrice SalesAmount
    	from categories c
    	join Products p on c.CategoryID = p.CategoryID
    	join [Order Details] d on d.ProductID = p.ProductID
    	join Orders o on o.OrderID = d.OrderID
    ) T
    pivot (sum(Salesamount) for OrderYEar in ([1996], [1997], [1998])) P
    --- Variant 2
    select CategoryName
    , sum([1996]) [1996] 
    , sum([1997]) [1997] 
    , sum([1998]) [1998] 
    from (
    	select 
    	c.CategoryName
    	, YEAR(o.orderdate) OrderYear
    	, case when year(orderdate) = 1996 then d.Quantity * d.UnitPrice else 0 end [1996]
    	, case when year(orderdate) = 1997 then d.Quantity * d.UnitPrice else 0 end [1997]
    	, case when year(orderdate) = 1998 then d.Quantity * d.UnitPrice else 0 end [1998]
    	from categories c
    	join Products p on c.CategoryID = p.CategoryID
    	join [Order Details] d on d.ProductID = p.ProductID
    	join Orders o on o.OrderID = d.OrderID
    ) T
    group by CategoryName
    --- Variant 3
    select Categoryname, [1996], [1997], [1998]
    from (
    	select
    	CategoryName, OrderYear, Sum(Salesamount) SalesAmount
    	from (
    		select 
    		c.CategoryName
    		, YEAR(o.orderdate) OrderYear
    		, d.Quantity * d.UnitPrice SalesAmount
    		from categories c
    		join Products p on c.CategoryID = p.CategoryID
    		join [Order Details] d on d.ProductID = p.ProductID
    		join Orders o on o.OrderID = d.OrderID
    	) T
    	group by CategoryName, Orderyear
    ) X
    pivot 
    (sum(salesamount) for Orderyear in ([1996], [1997], [1998])) P
    --- Variant 4
    select Categoryname
    , sum(case when OrderYear = 1996 then salesamount else 0 end) [1996]
    , sum(case when OrderYear = 1997 then salesamount else 0 end) [1997]
    , sum(case when OrderYear = 1998 then salesamount else 0 end) [1998]
    from (
    	select
    	CategoryName, OrderYear, Sum(Salesamount) SalesAmount
    	from (
    		select 
    		c.CategoryName
    		, YEAR(o.orderdate) OrderYear
    		, d.Quantity * d.UnitPrice SalesAmount
    		from categories c
    		join Products p on c.CategoryID = p.CategoryID
    		join [Order Details] d on d.ProductID = p.ProductID
    		join Orders o on o.OrderID = d.OrderID
    	) T
    	group by CategoryName, Orderyear
    ) X
    group by CategoryName


    Henn Sarv


    Friday, January 11, 2013 9:20 AM

Answers

  • My preference is for #4, but #2 is OK by me. I strongly recommend not using #1 or #3. The PIVOT operator is non-intuive, inflexible and non-standard. using aggregated CASE expression gives you much more flexibility.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 11, 2013 11:18 PM

All replies

  • >I've created during one training 4 different query producing same result

    I only know of 2 ways of PIVOTing:

    1. CASE expression

    2. PIVOT clause

    You may be able to create several variations.

    Did you look at the execution plans?


    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Friday, January 11, 2013 10:02 AM
    Moderator
  • Concerning above 4 queries perfomance you may check if adding

    WHERE  YEAR(o.orderdate) in (1996, 1997, 1998)

    to inner Select  will help.


    Serg

    Friday, January 11, 2013 10:24 AM
  • The only 2 generally used  code for pivot  is your Variant 1 and Variant 2. Also in Variant 2 you can move the SUM with CASE expression which will avoid the use of subquery

    Thanks and regards, Rishabh K

    Friday, January 11, 2013 10:34 AM
  • Yes I checked Execution plan

    In my NW the 3rd and 4th was equal, but might I've added some indexes during training

    I was surpriced that 1st and 2nd generates different Execution plan but all 4 variants works on NW quite equal time

    Henn


    Henn Sarv

    Friday, January 11, 2013 2:03 PM
  • I would use version 1.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, January 11, 2013 5:44 PM
    Moderator
  • My preference is for #4, but #2 is OK by me. I strongly recommend not using #1 or #3. The PIVOT operator is non-intuive, inflexible and non-standard. using aggregated CASE expression gives you much more flexibility.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 11, 2013 11:18 PM