The power of PIVOT in SQL Server 2005

With the arrival of SQL Server 2005, we have many new features available to make the most out of it. Today's post is going to be about one of the greatest new functionalities, that is, the powerful PIVOT sentence which can be used for converting rows into columns (and vice versa) in order to have a vision much more structured and meaningful of the data result sets.

So, moving on the example, we are going to use some tables of the 'Northwind' database like 'Orders', 'Orders Details', 'Employees', and 'Products'. The objective is to show the total value sold by each employee for the following products: 'Alice Mutton', 'Filo Mix', 'Flotemysost', 'Geitost', 'Konbu', 'Maxilaku', 'Pavlova', 'Tofu', and  'Vegie-spread'. Well, in SQL Server 2000, it can be done by using INNER JOINs and 3 Subqueries (if you had more tables to query, it would become a complex work to do). As a result of this, not only does the query become complex, but also it is not the most optimum choice.

SELECT Ventas1.*

FROM (SELECT Empleado, 

        (SELECT Products.ProductName FROM Products    

          WHERE Products.Productid=Ventas.ProductID) Producto, 

                SUM (ventas.ValorVendido)Total

      FROM (SELECT (C.lastname + ‘ ‘ + c.firstname)Empleado,ProductId,

                 (OD.Unitprice*OD.Quantity) [ValorVendido]

            FROM [Order Details] OD

             INNER JOIN ( [Orders] O        

              INNER JOIN Employees C

            ON O.Employeeid=C.Employeeid)

             ON OD.Orderid=O.OrderID) Ventas

GROUP BY Empleado, Productid) Ventas1

WHERE Producto IN(‘Alice Mutton’,‘Filo Mix’,‘Flotemysost’,‘Geitost’,

‘Konbu’,‘Maxilaku’,‘Pavlova’,‘Tofu’,‘Vegie-spread’)

Here we can see the partial result:



Having seen how difficult some queries can become by using old-fashioned and traditional techniques, now we are going rewrite the query to use PIVOT. Here is the code:

SELECT Empleado, [Alice Mutton],[Filo Mix],[Flotemysost],

      [Geitost],[Konbu], [Maxilaku],[Pavlova],[Tofu],[Vegie-spread]

FROM (SELECT Empleado, 

             (SELECT PRoducts.Productname FROM Products

             WHERE Products.Productid=Ventas.Productid) Producto,

                 SUM (ventas.ValorVendido) Total

      FROM (SELECT (C.lastname+ ‘ ‘ + C.firstname) Empleado, ProductID, 

               (OD.Unitprice*OD.Quantity) [ValorVendido]

            FROM [Order Details]OD

                INNER JOIN ([Orders] O

                INNER JOIN Employees C

             ON O.EmployeeID=C.EmployeeID

        )

ON OD.OrderID=O.OrderID) Ventas

GROUP BY Empleado,Productid) ventas1

PIVOT(SUM(total) FOR [Producto] IN ([Alice Mutton],[Filo Mix],[Flotemysost],

[Geitost],[Konbu],[Maxilaku],[Pavlova],[Tofu],[Vegie-spread])) AS pvt

Only after successfully familiarising with PIVOT will we realise how easy and practical is use it so as to save not only time but also costs. More details about it can be found by checking the following resource http://msdn2.microsoft.com/es-es/library/ms177410.aspx.

That is all for now until next post. Let me know any remarks and experience you have may using PIVOT. Thanks for reading. Stay tuned.