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.

How to connect to SQL Server 2005 much faster via SQL Management Studio

While connecting with SQL Server 2005, we have to deal with the 'splash' screen and then indicate, basically, the following information such as service type, server name, and authentication type. This process may sometimes slow us down, nevertheless, we can optimise it as long as we know where we want to establish a connection. Put differently, we can save some time by using 'sqlwb.exe' tool which allows to start SQL Management Studio rapidly. This tool has some parameters we can use them to speed up the logon process to SQL instances. Here I am coming up with an illustration how to use 'sqlwb.exe' with some basic parameters. For instance,  if we would like to connect to the 'TestDB' database on 'SVPRDB1' server and with Windows Authentication, it can be done in this way:

sqlwb.exe -E -S SVPRDB1 -d TestDB -nosplash

Only after successfully executing that will we connect directly to 'TestDB' database without specifying manually the parameters and also without seeing 'the splash' screen showing the presentation of SQL Management Studio version. Now we can also customise the shortcut as it is shown in the following picture:



As earlier I said, this is the fastest way to connect to SQL Server which will allow to save some seconds. It couldn't have been simpler. That is all for now. Thanks for reading. Stay tuned.