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).
    1. If you need a sample database, or would like to follow my example, I downloaded the AdventureWorks2008R2 database file found here.
    2. 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

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 something like subdomain.domain.com, 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.

Pivot Table using SQL

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.


Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course

Still not convinced? Check out my review of the course!

  • Jacob

    Great article! I learned a few things I definitely did not know. I would love to learn more about index and match functions…could you do an article about them?

  • Anonymous

    Just wanted to drop a line and say thanks. I trouble finding this online for a project I’ve been working on at work and your instructions were impeccable.

    Thanks!

    • 😀 Thanks for the kind comments! I’m glad you found it useful.

  • adaobi

    this is a great article for people who would want to access information on a pivot table. however, I want to know what to do should there be multiple occurring fields. in my experience, the rows and columns are duplicating such that there is need to add up the resultant data. this can be very stressful especially when one has to analyse over 1000 indicators for about 600 organizations. what do you advise?

  • Emily

    I appreciate this topic very much. I do have a question. How do I reference a stored procedure when creating a pivot table? I see tables and views available, but not stored procedures. Any help is appreciated!

    • Hi Emily,

      Glad you like it! Follow the instructions in this post up to where you would paste in a SQL statement (just choose any old table when setting up the external connection and when you get to the properties page, you’ll switch it from Table to SQL) and in the SQL statement you would use something like this

      EXEC myStoredProcedure parameter1, parameter2;

      Please let me know if this helps.

      Joseph

  • disqus_h7CCGFFDkH

    Hi,

    I have large set of data from 2012 to end 2015, this is within an Excel spreadsheet, I then have an embedded SQL which runs from 2016 – to date. The two sets of data are then collated into a pivot table and graph. However the data from the 2012 – 2015 is showing as a separate set to the embedded data in the pivot table, even though the formatting is the same.

    I am unable to run the embedded SQL from 2012 to date due to the size, and running out of spool space.

    Any ideas on how to get the data to read as if was from the same set (preferably without copy/paste as need the embedded SQL to stay live)?

    Thanks,

    Matt

    • Joseph

      Hi Matt,

      There is a way to do what you’re asking, but it takes a decent amount of explanation. Basically, you can join multiple tables in the same Pivot Table. I am going to make a post about this exact example you mention because I think it would be a good exercise to go through.

      In the meantime, I don’t want you to be stuck for too long. Here’s what I would do:
      – Create the embedded SQL report into Excel, but don’t use PivotTable to show the results, but rather select “Table” when asked how to display the data.
      – Then, with your other Table, make sure it’s the same format, as you said it was
      – Then follow this guide: https://support.office.com/en-us/article/Consolidate-multiple-worksheets-into-one-PivotTable-report-3ae257d2-ca94-49ff-a481-e9fc8adeeeb5 – however, when referencing the multiple tables, use the Table Names instead of the explicit Cell References

      That should give you a consolidated Pivot Table with both tables as your data sources. If you have your data as Excel Tables, then they will expand automatically and when you add data to them, you just have to Right-Click in the Pivot Table and choose Refresh

      I hope this helps. I will come back to this at a later time to let you know when I have the post created that goes through your example.

      Thank you,
      Joseph