While fixing the crash (a trivial error) I also fixed some issues of bad recognition of some column types and length by discovery.
Concerning your example, some remarks:
After fixing, your sample table is displayed as:
pk |
ts |
merchant |
amount |
Meals & Tips |
Other |
Phone |
Transportation (Taxi,bus,train etc.) |
Uncategorized |
1 |
2014-02-27 |
Hotel la Airport 73los Angeles |
492.22 |
|
|
|
|
Thursday |
2 |
2014-02-26 |
Hotel lax Andiamo 7los Angeles |
209.31 |
|
|
|
|
Wednesday |
3 |
2014-02-25 |
Hotel lax Andiamo 7los Angeles |
58.14 |
|
|
|
|
Tuesday |
4 |
2014-02-25 |
Hotel lax Bistro 73los Angeles |
3.73 |
|
|
|
|
Tuesday |
5 |
2014-02-24 |
Airplane Ticket |
756.00 |
Monday |
|
|
|
|
6 |
2014-02-21 |
Restaurant |
33.01 |
|
|
|
|
Friday |
7 |
2014-02-21 |
Cafe & Bakery |
5.75 |
|
|
|
|
Friday |
8 |
2014-02-18 |
Airplane Ticket |
380.00 |
Tuesday |
|
|
|
|
9 |
2014-02-18 |
Hotels |
542.18 |
|
|
|
|
Tuesday |
10 |
2014-02-17 |
Purchase Finance Charge |
129.00 |
|
Monday |
|
|
|
11 |
2014-02-15 |
Bistro |
36.46 |
|
|
|
|
Saturday |
12 |
2014-02-14 |
Cafe & Bakery |
11.72 |
|
|
|
|
Friday |
13 |
2014-02-13 |
Airplane Ticket |
283.00 |
Thursday |
|
|
|
|
14 |
2014-02-13 |
Coffee & Tea |
2.15 |
|
|
|
|
Thursday |
15 |
2014-02-12 |
Pfd Parking 00-seattle |
7.00 |
|
|
|
Wednesday |
|
16 |
2014-02-10 |
Airplane Ticket |
272.00 |
Monday |
|
|
|
|
17 |
2014-02-10 |
Airplane Ticket |
299.00 |
Monday |
|
|
|
|
18 |
2014-02-10 |
Phone Company |
303.00 |
|
|
Monday |
|
|
This is meaningless due to the source tables and the way it is pivoted.
Firstly, "GroupBy=6" does not make sense. The GroupBy option is a boolean option to be set to true only when the source table has the format of a group by query result. One the other hand, the FncCol is taken by default as he last column (wd), which I doubt is what you intend.
The FncCol is generally a numeric column (unless you use the MIN or MAX function) in particular with the default SUM function.
I presume it should be amount, the only numeric column of the source table.
However, even changing "GroupBY=6" by "FncCol=amount" will not give an interesting result:
CREATE TABLE my_pivot_2
ENGINE=CONNECT TABLE_TYPE=pivot TABNAME=test_pivot_2
OPTION_LIST='PivotCol=category,FncCol=amount';
This is because the source table has columns that make the internal grouping still have the same number of rows than the source table (in particular the pk column)
More interesting result can be obtain by removing from the pivot table these extra columns. For instance, to have a pivot display of the
sum of amount by merchant and category, the pk, ts, and wd columns must be dropped from the pivot table.
There are several ways to do that. For instance:
ALTER TABLE my_pivot_2 drop pk, drop ts, drop wd;
Another way is to make the pivot table on a restricted source table:
CREATE VIEW test_pivot_1 as select merchant, category, amount from test_pivot_2;
CREATE TABLE my_pivot_2
ENGINE=CONNECT TABLE_TYPE=pivot TABNAME=test_pivot_1
OPTION_LIST='PivotCol=category,fnccol=amount';
Once done, SELECT * FROM my_pivot_2 will display:
merchant |
Meals & Tips |
Other |
Phone |
Transportation (Taxi,bus,train etc.) |
Uncategorized |
Airplane Ticket |
1990.00 |
0.00 |
0.00 |
0.00 |
0.00 |
Bistro |
0.00 |
0.00 |
0.00 |
0.00 |
36.46 |
Cafe & Bakery |
0.00 |
0.00 |
0.00 |
0.00 |
17.47 |
Coffee & Tea |
0.00 |
0.00 |
0.00 |
0.00 |
2.15 |
Hotel la Airport 73los Angeles |
0.00 |
0.00 |
0.00 |
0.00 |
492.22 |
Hotel lax Andiamo 7los Angeles |
0.00 |
0.00 |
0.00 |
0.00 |
267.45 |
Hotel lax Bistro 73los Angeles |
0.00 |
0.00 |
0.00 |
0.00 |
3.73 |
Hotels |
0.00 |
0.00 |
0.00 |
0.00 |
542.18 |
Pfd Parking 00-seattle |
0.00 |
0.00 |
0.00 |
7.00 |
0.00 |
Phone Company |
0.00 |
0.00 |
303.00 |
0.00 |
0.00 |
Purchase Finance Charge |
0.00 |
129.00 |
0.00 |
0.00 |
0.00 |
Restaurant |
0.00 |
0.00 |
0.00 |
0.00 |
33.01 |
works for me in Debian 7
crashes for me in the described way on Windows 7 64Bit