Pivot Function

Normalization principle dictate that we keep our tables as narrow as possible, i.e. only few columns wide to keep the table schema simple and performant.

It does create a problem when you need to display data in a columnar format or pivot format. For example take a look at the following query...

SELECT C.Name, O.Total As Total,O.Year
FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID 
The result will look something like this...

 

 

 

 

but what if you want to display the information in a columnar format, like an excel spreadsheet with Names going vertically but years going horizontally.

Using Pivot function you can achieve just that. Take a look at the query below...

SELECT * FROM
(SELECT C.Name, O.Total As Total,O.Year
FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID
) A

PIVOT
( SUM(Total) FOR Year IN ([2006],[2007])

) AS TotalAmt

The result will be as follows...

 Data is displayed in much more readable and organized manner.


PIVOT function allows you to pivot a column for another column, as we did in the above example, i.e. pivot Total for Year.

You may be wondering about using nested query. If you don't use nested query you will get a duplicate field error. This is because as you can see from the previous result set, there are multiple customers with same name and we are joining two tables (Orders and Customers) on Customer ID field which results in duplicate field error when trying to pivot.

Thank you.

 

Exporting data from SQL Server to Excel

Excel is a very powerful tool and it can come in handy if you just want to build a quick report from your SQL Server database. In this post, we will discuss ways to export data and create a quick employee report.

1. Fire up your Excel and browse to data tab, click on From Other Sources and select From SQL Server.

2. Enter the SQL Server name. If your SQL Server's instance is other than default, you will also need to enter the instance name.

You will have to do this only once. After successfully connecting to your database, you can save the connection string and in the future just click on Existing Connections from the data tab to reuse the connection.

3. After entering the information, click on Next and select the database you want to export data from. In this example, we are going to use AdventureWorks. Select a table or a view to retrieve data from and click Next.

4. This is your chance to save your connection string for any future usage. Rename the connection string to a more appropriate name and click on Finish.

5. You are now ready to export data. You can either export table as it is saved in the database or create a pivot report or a pivot report with chart. In the example, I chose the second option.

6. Properties tab allows you to set certain parameters. Generally defaults work OK for most situations.

7. When you click on OK, Excel lists all the fields that are available in your selected table or view and you can select the ones you want to show up on report. As you select a column, you will notice that the value is added in the spreadsheet. The order you want the data to appear in the report is the order you should select the columns. For example, if you want Last Name to appear before First, select Last Name first and then select First Name column.

8. Close the Fields List and here is your nice little report.

Thank you.