Creating a pivot table which resembles a SQL 'group by'
 

Hi all,

I was hoping you can help me with this question: how do I build a
pivot table which looks like a "group by" operation in a database?

Let's say I have this table:

Continent Product Sales
Asia Red 100
Asia Yellow 200
Asia Red 100
Asia Yellow 300
Europe Red 100
Europe Yellow 200
Europe Red 100
Europe Yellow 300


An Excel Pivot table will return this:

Row Labels Sum of Total sales
Asia 700
Red 200
Yellow 500
Europe 700
Red 200
Yellow 500
Grand Total 1400

whereas a statement like:

select Continent, Product, sum(Sales) as TotalSales
from MyTable
group by Continent, Product

in SQL will return:

Continent Product Total sales
Asia Red 200
Asia Yellow 500
Europe Red 200
Europe Yellow 500

Is there an easy way to obtain the latter in Excel?
Thanks!

Creating a pivot table which resembles a SQL 'group by'
 

One way -

Create a "new" set of data that translates your 3 columns of data into 2 by
concatenating the Continent and Product columns. Assuming Continent is in
column A and Product in B, then

=A2&" - "&B2

will give you

Asia - Red

Then build your pivot table using the new, concatenated column instead of
the distinct Continent and Product columns


" wrote:

Hi all,

I was hoping you can help me with this question: how do I build a
pivot table which looks like a "group by" operation in a database?

Let's say I have this table:

Continent Product Sales
Asia Red 100
Asia Yellow 200
Asia Red 100
Asia Yellow 300
Europe Red 100
Europe Yellow 200
Europe Red 100
Europe Yellow 300


An Excel Pivot table will return this:

Row Labels Sum of Total sales
Asia 700
Red 200
Yellow 500
Europe 700
Red 200
Yellow 500
Grand Total 1400

whereas a statement like:

select Continent, Product, sum(Sales) as TotalSales
from MyTable
group by Continent, Product

in SQL will return:

Continent Product Total sales
Asia Red 200
Asia Yellow 500
Europe Red 200
Europe Yellow 500

Is there an easy way to obtain the latter in Excel?
Thanks!

Creating a pivot table which resembles a SQL 'group by'
 

I'm afraid that's not feasible, as I need to change the structure of
my pivots on the fly. In other words, I cannot know from the beginning
which fields I will be grouping by, and preparing concatenated fields
for all possible combinations doesn't work - it's like killing a
mosquito with a nuclear bomb :)
I have found a slightly better solution: in Excel 2007: go to design --
report layout -- show in tabular form. Not quite the same result as
in SQL, but I guess it's the closest the piece of junk aka as Excel
can get!

On Aug 7, 1:14*pm, Duke Carey
wrote:
One way -

Create a "new" set of data that translates your 3 columns of data into 2 by
concatenating the Continent and Product columns. *Assuming Continent is in
column A and Product in B, then

=A2&" - "&B2

will give you

Asia - Red

Then build your pivot table using the new, concatenated column instead of
the distinct Continent and Product columns