Pivot from Columns to Rows for Google Pie Chart

[Staple]

Hiya,

I am trying to get data out of a Google spreadsheet to make some
charts. I have it working well for line charts but need to get a pie
chart of column sums.

You can see it here...

http://www.netmechanics.co.nz/eg/grapher/index2.htm

My query is for example - select sum(B), sum(C) where date '2008-1-1'
<= A and date '2010-1-1' >= A

I think I need to pivot it to get a column of labels and a column of
totals ... does anybody know how I would do this?

The spreadsheet is here...

https://spreadsheets.google.com/spreadsheet/pub?key=0ArQoG4oepsJldFhYVGQ3SGVjYS15YXpFbDRBRlNYZ2c&output=html

Many thanks...

[asgallant]

Use the pivot function in the query: http://code.google.com/apis/chart/interactive/docs/querylanguage.html#Pivot

[Staple]

Thanks - I've tried that to no avail so far, can you possibly give me
a clue/example?

On Jul 28, 1:10 am, asgallant <drew_gall...@abtassoc.com> wrote:
> Use the pivot function in the query:http://code.google.com/apis/chart/interactive/docs/querylanguage.html...

[asgallant]

try this: http://code.google.com/apis/ajax/playground/?type=visualization#more_query_options

[Staple]

Er... I must be an idiot. I've looked and looked and can not find
anywhere I can pivot two rows into two columns ... I need to go
select sum(B), sum(C) sum (F) (which I am hoping will return the
headers plus the sum of the columns numbers) then pivot it down so I
get two columns presentable in a pie graph.

Ouch.

On Jul 29, 1:35 am, asgallant <drew_gall...@abtassoc.com> wrote:
> try this:http://code.google.com/apis/ajax/playground/?type=visualization#more_...

[asgallant]

Ok, I get what you want to do now.  I don't think the query language can help you there, as the column labels are never returned as data points in a column when pivoting.  Query to get the sum of all columns, and pivot manually after you get your data back:

// assume data1 is a dataTable object with the data returned from your query, data2 is a dataTable set up however you like, and cols is the number of columns in data1:

for (i = 0; i < cols; i++) {

     data2.addRow([data1.getColumnLabel(i), data1.getValue(0, i)]);

}

then draw your chart with data2.