I've created an OLE DB Query that returns data from a SQL Server. I'd like
the data to appear in a pivot table the same way it appears when I run the
SQL query from Management Studio.

For example the data isn't grouped in SQL Query which is how I want it.
Project_ID | Resource | Hours
ProjectA | Bob | 8
ProjectA | Bob | 3
ProjectA | John | 5
ProjectB | John | 4

In excel I can't get my results to show like that. Instead I get the
following:
ProjectA | Bob | 8
| |3
| John | 5
ProjectB | John | 4

It groups the data for the columns. I'd like the data for the columns to
appear in every row so I can use this table in vlookups. Instead I get blank
cells because the data has been grouped together and is collapsible. How can
I get my SQL query to show properly in excel?

But that is what pivot tables do. The group and aggregate data together. Why
not just return the data directly to a sheet instead of to a pivot table.
Baring that here is a link to a page to help you manipulate the pivot inot
what you need (by making it not a pivot table any more)

http://www.contextures.com/xlfaqPivot.html#Repeat
--
HTH...

Jim Thomlinson


"Colin" wrote:

I've created an OLE DB Query that returns data from a SQL Server. I'd like
the data to appear in a pivot table the same way it appears when I run the
SQL query from Management Studio.

For example the data isn't grouped in SQL Query which is how I want it.
Project_ID | Resource | Hours
ProjectA | Bob | 8
ProjectA | Bob | 3
ProjectA | John | 5
ProjectB | John | 4

In excel I can't get my results to show like that. Instead I get the
following:
ProjectA | Bob | 8
| |3
| John | 5
ProjectB | John | 4

It groups the data for the columns. I'd like the data for the columns to
appear in every row so I can use this table in vlookups. Instead I get blank
cells because the data has been grouped together and is collapsible. How can
I get my SQL query to show properly in excel?

Sounds logical. How do I return a query directly to a sheet? I only know how
to grab external data using a pivot table.

"Jim Thomlinson" wrote:

But that is what pivot tables do. The group and aggregate data together. Why
not just return the data directly to a sheet instead of to a pivot table.
Baring that here is a link to a page to help you manipulate the pivot inot
what you need (by making it not a pivot table any more)

http://www.contextures.com/xlfaqPivot.html#Repeat
--
HTH...

Jim Thomlinson


"Colin" wrote:

I've created an OLE DB Query that returns data from a SQL Server. I'd like
the data to appear in a pivot table the same way it appears when I run the
SQL query from Management Studio.

For example the data isn't grouped in SQL Query which is how I want it.
Project_ID | Resource | Hours
ProjectA | Bob | 8
ProjectA | Bob | 3
ProjectA | John | 5
ProjectB | John | 4

In excel I can't get my results to show like that. Instead I get the
following:
ProjectA | Bob | 8
| |3
| John | 5
ProjectB | John | 4

It groups the data for the columns. I'd like the data for the columns to
appear in every row so I can use this table in vlookups. Instead I get blank
cells because the data has been grouped together and is collapsible. How can
I get my SQL query to show properly in excel?

Forget it. How simple. Guess I really am a Newbie with excel.

For all other newbs:
Click Data tab
Click Existing Connections and choose table

"Colin" wrote:

Sounds logical. How do I return a query directly to a sheet? I only know how
to grab external data using a pivot table.

"Jim Thomlinson" wrote:

But that is what pivot tables do. The group and aggregate data together. Why
not just return the data directly to a sheet instead of to a pivot table.
Baring that here is a link to a page to help you manipulate the pivot inot
what you need (by making it not a pivot table any more)

http://www.contextures.com/xlfaqPivot.html#Repeat
--
HTH...

Jim Thomlinson


"Colin" wrote:

I've created an OLE DB Query that returns data from a SQL Server. I'd like
the data to appear in a pivot table the same way it appears when I run the
SQL query from Management Studio.

For example the data isn't grouped in SQL Query which is how I want it.
Project_ID | Resource | Hours
ProjectA | Bob | 8
ProjectA | Bob | 3
ProjectA | John | 5
ProjectB | John | 4

In excel I can't get my results to show like that. Instead I get the
following:
ProjectA | Bob | 8
| |3
| John | 5
ProjectB | John | 4

It groups the data for the columns. I'd like the data for the columns to
appear in every row so I can use this table in vlookups. Instead I get blank
cells because the data has been grouped together and is collapsible. How can
I get my SQL query to show properly in excel?