UNPIVOT not working

[msalamon1]

I am trying a fairly simple UNPIVOT example, but it's not working.

My data is like this, with 4 columns:

var unpivotData = [ {Quarter:'Q1',R1Sales:20,R2Sales:30,R3Sales:50},.. ];

my query:

var unpivotQuery = "Select Quarter, R1Sales, R2Sales, R3Sales from ? UNPIVOT (Sales for Region in (R1Sales, R2Sales, R3Sales) ) as unpvt";

which is virtually identical to the query found here: https://github.com/agershun/alasql/issues/490

but when I run it:

var unpivotResults = alasql( unpivotQuery, [unpivotData]);

i get this error:

Uncaught Error: Parse error on line 1: ...ales from ? UNPIVOT (Sales for Region in -----------------------^ Expecting 'EOF', 'WITH', 'COMMA', 'RPAR', 'ORDER', 'WHERE', 'UNION', 'INTERSECT', 'EXCEPT', 'CROSS', 'OUTER', 'NATURAL', 'JOIN', 'INNER', 'LEFT', 'RIGHT', 'FULL', 'SEMI', 'ANTI', 'ON', 'USING', 'GROUP', 'LIMIT', 'END', 'ELSE', 'SEMICOLON', 'GO', got 'LPAR'parseError @ alasql.min.js:3parse @ alasql.min.js:3alasql.parse @ alasql.min.js:3alasql.dexec @ alasql.min.js:4alasql.exec @ alasql.min.js:4alasql @ alasql.min.js:1(anonymous function) @ join2.html:73

what am I doing wrong?

also, is there any way to use this query format instead:

var unpivotQuery = "Select Quarter, Region, Sales from ? UNPIVOT (Sales for Region in (R1Sales, R2Sales, R3Sales) ) as unpvt";

or even this one (with aggregation):

var unpivotQuery = "Select Quarter, Region, sum(Sales) as [Total Sales] from ? UNPIVOT (Sales for Region in (R1Sales, R2Sales, R3Sales) ) as unpvt group by Quarter, Region";

[mathiasrw]

I see there is a test covering unpivot so am suprised you get a parse error: https://github.com/agershun/alasql/blob/090333092ec7094b0889069af78967e441fb5e2a/test/test359.js#L49-L54

Would it be posible for you to try to put data in a table and replace the ? with the table name and let me know if that works?

[msalamon1]

I received the same error using a table.

Side question: is there any performance or memory benefit to using tables rather than just storing the data directly as an array of JS objects?

Maybe i'm doing something slightly wrong which is unrelated to the UNPIVOT? but I can run a select * query on the data.

[msalamon1]

Btw, I tried the test on that page and I received the same error.

[mathiasrw]

Very strange. Could you do me a favor and let me know what you get when printing alasql.version?

[msalamon1]

Mathias, I think that was it. :(  .1.9.   I'm not sure why I was pointing to an older version which was local.   My production use has used the CDN .2.3 version, but this test was against an older, local copy.  Sorry about wasting your time on that.  :(  it seems to be working now. Btw, this query format worked:  "Select Q, R1Sales, R2Sales, R3Sales from ? UNPIVOT (Sales for Region in (R1Sales, R2Sales, R3Sales) ) as unpvt";

but this one did not: "Select Q, Region, Sales from ? UNPIVOT (Sales for Region in (R1Sales, R2Sales, R3Sales) ) as unpvt"; do you plan on supporting that format in the future?

Mark

On Friday, March 11, 2016 1:24 PM, Mathias Rangel Wulff <notifications@github.com> wrote:

Very strange. Could you do me a favor and let me know what you get when printing alasql.version— Reply to this email directly or view it on GitHub.

[mathiasrw]

Im glad we nailed it (and that there was no bug).

Ill create a new issue with your question as a feature request. We dont have any plans on what to implement.

[msalamon1]

thanks!  again, sorry for wasting your time on that.

On Friday, March 11, 2016 2:01 PM, Mathias Rangel Wulff <notifications@github.com> wrote:

Im glad we nailed it (and that there was no bug).Ill create a new issue with your question as a feature request. We dont have any plans on what to implement. — Reply to this email directly or view it on GitHub.