Emil Drkusic
SQL Server and MS Excel - report data, pivot table, and chart

Learn SQL: SQL Server export to Excel

May 20, 2020 by

In the previous article, Learn SQL: SQL Server Pivot Tables, we’ve discussed how to create a report using a PIVOT table query. We’ve created such a report directly in the SQL Server. In this article, we’ll do the same, but this time we’ll use SQL Server export to Excel. So, let’s start.

Data model and the general idea

The data model we’ll use is the same that we’ve been using throughout this series. Even the queries used are the ones we’ve analyzed in the previous article and therefore I won’t explain them in-depth here. In case, you’ve missed the previous article, I strongly recommend taking a look at how these queries work.

SQL Server and MS Excel - the data model we'll use

The goal of this article is to show how we could combine data returned using SQL query and SQL Server export to Excel. This combination could prove to be very powerful. One of the main reasons is that MS Excel provides many additional options like graphs, calculations, formatting results. Also, you could use one “master” Excel workbook where you would join data from multiple sources, much easier than you could do it in case you read these data from original sources. And, worth to mention, that Excel is widespread and people are usually much more familiar with it than with databases.

SQL Server export to Excel – Using PIVOT Query

We want to create a report that contains a list of all cities from our database. For each city, we need to know a number of calls grouped by their outcome.

To achieve that, we’ll use the final query from the previous article. This query shall always return all combinations of cities and call outcomes (we’ve used CROSS JOIN to create reporting categories). Also, we’ll be able to pivot by outcome (name), because our query is dynamic (we’re adding outcomes to the pivot part of the query based on the current contents of the dictionary). Therefore, our query shall work in all cases – even if we add new outcomes to the dictionary.

Let’s take a look at the query result now. As expected, the result is the pivot table with all the data we need. Now is the time to use SQL Server export to Excel.

dynamic SQL Server PIVOT table query

SQL Server is a great tool and it has many addons you could use for specific purposes. Still, one of the frequently used options are to simply copy the SQL Server query result to MS Excel and make desired additional transformations there. The reason for that is that MS Excel provides a variety of options to present data. Some of the most commonly used are tables, pivot tables, many different graph types, etc. Also, you could use MS Excel workbook as a central place where you will gather data from different sources (based on different formats or technologies) and combine them to get an overall insight at your business. One more thing worth mentioning here is that MS Excel is widespread and people are used to working with it. I don’t have an actual statistic related to SQL Server and MS Excel usage, and I guess it would be hard to have actual numbers, so I’ll go with estimations available online. Around 750 million people worldwide use Excel, while in 2019 we had around 26.5 million developers (database developers are a small part of that number).

export SQL Server query result

After you run the query, you can simply select the whole query result by clicking in the upper left corner (above row numbers and left of the column names). The next thing to do is to select the “Copy with headers” option from the drop-down list.

pivot table and chart in MS Excel

We’ve pasted the query result in the MS Excel worksheet. In real-life examples, we could have more formatting than just applying bold to the categories. The graph below the table with data is prepared and formatted so it will change accordingly changes in the data.

There are a few more things we could have done differently here:

  • We could paste data (pivot table) in a separate worksheet and have the “presentation” worksheet formatted additionally
  • In case we have more details, we could prepare several different graphs, as well as calculate parameters relevant to our business

I’ve used this approach on real-world data as it enables you to create very complex dashboards after running a few queries. Of course, you’ll spend a lot of time to prepare dashboards (and adjust them accordingly changes in reporting requirements), but once you do that, you’ll profit a lot each time you need to create a new report.

And even better, this is not only limited to SQL Server export to Excel combination, but you could extract data from different sources (other DBMSs, files, text…) and use your dashboard as a central place to get an overall insight.

SQL Server export to Excel – Without PIVOT Query

The final goal of our task is to create a pivot table as well as a graph to visually represent the result. The approach in the previous section was to create a PIVOT table query directly in SQL and paste this query to Excel. There are (at least) 4 good reasons when you won’t always be able to use this approach:

  • You have the SQL query ready and you don’t want to waste additional time turning this query into the PIVOT table query, but rather spend that time manipulating the result in the Excel
  • Maybe you don’t use SQL Server (and MS Excel) and your DBMS simply doesn’t support PIVOT table queries (and dynamic queries which is what we need)
  • You want to have data in the raw format because you’ll need it for other purposes as well. Maybe you’ll create more than 1 pivot table from the data returned. Maybe you’ll combine these data with other data directly in Excel
  • You simply don’t know how to create a PIVOT table query in SQL Server but you know how to do it in MS Excel. OK, this is not an excuse, but still, this is a common case in real life

Let’s now see what can be done if we go with the option that we don’t use the SQL Server pivot query. Once more we’ll use the query described in the previous article. It’s almost the same as the previous query, with the main difference that we lack the PIVOT part of the query.

SQL Server query output

The first step is done. We’ve successfully returned all the data we need. Our result contains all combinations of cities and possible outcomes (we’ve used CROSS JOIN) and one row per each call (NULL where we had no call).

creating a pivot table in MS Excel

Now we’re ready to create a PIVOT table directly in Excel. After clicking on the “Insert” -> “Pivot Table” (in Tab List and Ribbon) the window like the one on the picture above shall appear. We’ll “Select a table or range” containing the data and the location where we want to place our pivot table (in our case that is “Existing Worksheet”, starting from cell G2).

report data and picot table in MS Excel

The next thing we did was to define what goes where. City names shall be “Rows” of our table, outcome texts shall go into “Columns” and we’ll count call duration (“Values”). Please notice that in the original table (output), I’ve replaced NULL values in column E, with blank spaces. This is important because the count function shall count 1 if the row contains any non-empty value.

SQL Server export to Excel - report data, pivot table, and chart

The last thing we’ll do is to create a graph. The data source for our graph is the pivot table created in the previous step. We’ll apply formatting to our graph (graph style, labels, axis, etc.), and we’re done.

This approach could be applied more generally compared to the approach when the pivot table is created directly in SQL Server. And that’s it – we’ve used SQL Server export to Excel to pull data from the database and create a simple dashboard.

Conclusion

Today we’ve seen how we could combine the powers of SQL Server export to Excel – using the SQL query to grab data, importing it to MS Excel, and making additional changes there. This is something you could easily do for various purposes and the limit is only your imagination (and, of course, business needs). The next step could be automating exports of tables or graphs created in MS Excel, directly to Word or PowerPoint.

Table of contents

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query?
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel

Emil Drkusic
Excel, SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby... You can find him on LinkedIn View all posts by Emil Drkusic

282 Views