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.
