Excel - Create a Pivot Table Using SQL

I use pivot tables extensively.  And there aren’t many days that I don’t create/use/manipulate pivot tables in some way.  However, I don’t typically use worksheet data like in most examples you’ll find online.  Instead, I usually create a custom SQL query to one of our SQL Server databases using SQL Server Management Studio (SSMS), test out the query, and then put the query inside an Excel pivot table in order to display the data.  And I’d like to share with you how I do that.

Prerequisites

1. Knowledge of SSMS and basic SQL queries

2. Some database to connect to (whether it’s local or on a server).

   • If you need a sample database, or would like to follow my example, I downloaded the AdventureWorks2008R2 database file found here.
   • In order to install a downloaded database in an mdf file format, please see Pinal Dave’s blog post here.  While the post is about 2012, I installed my database on 2008 which is why I needed the 2008 data file in the link above.
3. Basic knowledge of Pivot Tables

Does this article help you? If so, please consider supporting me with a coffee ☕️

SQL Query

In SSMS I usually play around with the query until I make sure I have all the info I need.  For this example, I’ll do something simple.  I’ll look for a person’s name, email, and city.

You can copy the code here:

SELECT
  'Name' = p.FirstName + ' ' + p.LastName
  , 'Email' = e.EmailAddress
  , 'City' = a.City
FROM
  Person.Person p
  INNER JOIN Person.EmailAddress e
    ON p.BusinessEntityID = e.BusinessEntityID
  INNER JOIN Person.BusinessEntityAddress bea
    ON bea.BusinessEntityID = p.BusinessEntityID
  INNER JOIN Person.Address a
    ON a.AddressID = bea.AddressID

Pivot Table Using SQL

Now it’s time to put that query inside a pivot table.  To do this, we need to start with grabbing the external data from SQL Server.

The Data Connection Wizard will pop up and request info about which server you wish to get the data from.

Normally, I would put some kind of URL there, but I’m writing this blog from home and I’ll be using SQL Server on my local machine.  So I’ll type in (local) (and yes, do keep the parentheses).  Windows Authentication will be fine for this example, however, at work you may need to use SQL Server username and password combination to connect to the database.  I’ll go over these options later because you may want to opt for one over the other.

Click Next and Excel will search for the server name we typed on the network (or on a computer if (local) was typed in).  We will be brought to the next window where we are asked to select the database and table we wish to connect to.

For our example I will be choosing the AdventureWorks2008R2 for the database and as for the table name, that can be any table name because we are going to modify the connection properties later to add our SQL query.

Click Next and we will be brought to the Save Data Connection File and Finish Window.  This window is meant to allow us to re-use the connection that we’re creating by saving a data connection file on our computer.  This file is meant to be like a template and does not need to be sent along with the Excel workbook if we want to give a copy to someone else.  Excel will save the settings within the workbook on its own.

Notice that the file name is automatically generated for us.  It is in the format of [Server][database] [Table].odc.  We don’t have to change this name because we don’t particularly care about the file.  Notice the Friendly Name text box.  This is actually the Connection Name in the Connection Properties dialogue which we’ll see in a minute.  Again, not of particular concern.  Only if you decide to use this connection name in VBA will it make sense to actually make the name meaningful (well, that’s what I think, anyway.  Feel free to comment below on how you use this name if you do).

Click Finish and we’ll be brought to the Import Data window.  This window asks us where to put the data in our workbook.

Notice the three options we are given: Table, PivotTable report, and PivotChart and PivotTable Report.  In the case of the Table option, you can simply import the data into an Excel table format (like when you go to Insert —> Table).  The PivotTable report is what we’re looking for.  It will insert a blank pivot table in the worksheet we choose.  The third option should be self-explanatory.

Do NOT Press OK yet!  Right now the connection is still set to the table we originally selected in the Select Database and Table window.  We want to use our SQL query instead, so we have to modify the connection.

Select PivotTable Report and then click Properties….  We will be brought to the Connection Properties window.  This is where we will add our SQL query.

Go to the Definition tab.  This is where the connection string is stored that tells Excel how and where to connect to the database (and what to do after that - like connecting to a table).  Here we want to change the connection type to SQL and then delete any text that was in the command text.  Now let’s paste in our SQL query from earlier and press OK.

“A Microsoft Warning?!?!  What?!  But I did everything you showed me!”  Relax!  It’s just Excel letting us know that the modifications we’re making will not be the same to the file that it stored on our computer.  Just press Yes.  We’ll be fine.

We’re brought back to the Import Data window.  The settings should still be the same (PivotTable Report, placed in =$A$1).  Press OK.

We’re given a blank pivot table to play with that has the data which our SQL query retrieved.  For this basic example, I’ll show the names of people grouped by city along with a count of all people in those cities.

To refresh this data, just right-click inside the table and select Refresh.

Windows Authentication Versus SQL Server Authentication

I didn’t feel this post would be complete without discussing some best practices about the authentication mode and when to choose between the two.  There are pros/cons between the two and it all depends on what you want to do with the workbook when you’re done with it.

If the workbook will only go to those people who actually have valid logins to the SQL Server using their Windows credentials, then Windows authentication will be fine.  If the workbook is set to use Windows Authentication and a user who is not authorized by the SQL Server, they will not be able to refresh the data.  They can see what data is there, but no refreshing of the sort.  They will actually be given a window that asks them for authorized credentials.

If you have no clue who will share this workbook, it’s probably best to use a SQL Server read-only user to access the database desired.  However, the workbook will not store the username and password for security purposes.  If you decide to use a SQL Server authorized read-only user (and I recommend this option), then you need to go to the Connection Properties window and check the Save Password option.  Otherwise, the user will be asked to authenticate every time you want to refresh the data.  Go to Data —> Connections —> Connection Name —> Properties…

Excel will ask you if you are sure you want to do this, click yes.  Notice the connection string has changed to include the username and password.  For this reason I highly recommend that you use a read-only user to do this.  You wouldn’t want someone with a little know-how to see an all-access username and password to your database and cause some serious damage to your tables.

One last note…

You might be asking yourself, “shouldn’t he be using a view or stored procedure instead?  Isn’t he showing the internals of the database too much?”  This is a valid question and I do recommend you create a view or a stored procedure for this kind of thing.  For the sake of brevity, I decided to show how an adhoc query can be used to create a pivot table.

Also, I recommend using a view/stored procedure for this because in the case that you send this workbook out to, let’s say, 5 people and you realize that you created a query that had an incorrect WHERE clause, it’s much easier to edit a view/stored procedure than to edit the workbook’s adhoc query and redistribute it to all 5 people again.

If you like this content and would like to be notified of new posts, please subscribe!

Questions?  Comments?  Please post a comment below!  I’d love to hear anything you would like me to write about or if you need me to clarify anything.