Is it possible to use Excel's pivot table as a kind of query builder against a database, without copying the entire database into Excel? I know that with the External Data Sources feature, you can connect to any ODBC database and create a pivot table out of the results from any SQL query. But at least with MySQL / ODBC, it seems to do this by copying the entire result set onto the local machine and then running the queries locally.

Is there a way to get the same functionality where the queries actually happen on the server, say by using Microsoft SQL server? My problem is that I want to be able to analyze data using pivottables, but the whole database table is too big to load into excel.


Even if you did copy the entire database (which would be incredibly large most likely), pivot tables are slow. It would take a lot of time to just get the pivot table to go through all of the data and display anything initially (I'm working with a pivot table about 30k rows and 20 columns, and it takes over 10 minutes to just load the file).

It is impossible to use a pivot table with anything other then a defined range of Excel values or a query, but the entire result set needs to be fetched first. Even if it were possible to use a pivot table directly with the database, pivot tables need to go through each row of data to parse it - effectively downloading the whole database anyways.

If you need Pivot Table functionality in your queries, look at the PIVOT and UNPIVOT commands for MS SQL, or if you're using MS Access, try using a cross-tab query to get you started. As a last resort, just filter out the rows and columns you don't need from the database before you start your pivot table.

  • Re: "impossible" - not true. When connecting to an external data source through ODBC you can send the data straight to a pivot table and never have the raw data visible in your worksheet. The bummer is that the full set of data appears to reside in excel somewhere anyway. – Leopd Aug 31 '11 at 20:45
  • @Leopd that's what I was getting at, but I updated my answer to be more explicit. – Breakthrough Aug 31 '11 at 21:00

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.