Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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.

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .