Alasql: PIVOT()

[agershun]

There is a request to add PIVOT functionality to AlaSQL.

[mathiasrw]

Sounds great!

There is some talks about PIVOT() here: https://github.com/agershun/alasql/issues/287

[agershun]

Yes, I just received request, may be this help us to finish with it :)

[mathiasrw]

[agershun]

I did this.

Unfortunately, it works still does not comply with standard T-SQL syntax (now AlaSQL processes data to pivot after SELECT function, but it should be performed immediatly after FROM part). But it works. PIVOT and UNPIVOT with SELECT *.

Here are the examples for PIVOT:

        select * from DailyIncome
        pivot (max (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDay
        where VendorId in ('SPIKE')

And UNPIVOT:

    SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
       FROM pvt
    UNPIVOT
       (Orders FOR Employee IN 
          (Emp1, Emp2, Emp3, Emp4, Emp5)
    )AS unpvt;

See text359.js and text380.js for other examples.

[mathiasrw]

Closed per request from #628

[lpgeiger]

@mathiasrw I'm working through the tests above. I see they work, but I don't see any documentation. I'm trying to work through the 'simple' pivot, with a JSON datasource. See below:

  var data = [
  { VendorId: 'SPIKE', IncomeDay: 'FRI', IncomeAmount: 100 },
  { VendorId: 'SPIKE', IncomeDay: 'MON', IncomeAmount: 300 },
  { VendorId: 'FREDS', IncomeDay: 'SUN', IncomeAmount: 400 },
  { VendorId: 'SPIKE', IncomeDay: 'WED', IncomeAmount: 500 },
  { VendorId: 'SPIKE', IncomeDay: 'TUE', IncomeAmount: 200 },
  { VendorId: 'JOHNS', IncomeDay: 'WED', IncomeAmount: 900 },
  { VendorId: 'SPIKE', IncomeDay: 'FRI', IncomeAmount: 100 },
  { VendorId: 'JOHNS', IncomeDay: 'MON', IncomeAmount: 300 },
  { VendorId: 'SPIKE', IncomeDay: 'SUN', IncomeAmount: 400 },
  { VendorId: 'JOHNS', IncomeDay: 'FRI', IncomeAmount: 300 },
  { VendorId: 'FREDS', IncomeDay: 'TUE', IncomeAmount: 500 },
  { VendorId: 'FREDS', IncomeDay: 'TUE', IncomeAmount: 200 },
  { VendorId: 'SPIKE', IncomeDay: 'MON', IncomeAmount: 900 },
  { VendorId: 'FREDS', IncomeDay: 'FRI', IncomeAmount: 900 },
  { VendorId: 'FREDS', IncomeDay: 'MON', IncomeAmount: 500 },
  { VendorId: 'JOHNS', IncomeDay: 'SUN', IncomeAmount: 600 },
  { VendorId: 'SPIKE', IncomeDay: 'FRI', IncomeAmount: 300 },
  { VendorId: 'SPIKE', IncomeDay: 'WED', IncomeAmount: 500 },
  { VendorId: 'SPIKE', IncomeDay: 'FRI', IncomeAmount: 300 },
  { VendorId: 'JOHNS', IncomeDay: 'THU', IncomeAmount: 800 },
  { VendorId: 'JOHNS', IncomeDay: 'SAT', IncomeAmount: 800 },
  { VendorId: 'SPIKE', IncomeDay: 'TUE', IncomeAmount: 100 },
  { VendorId: 'SPIKE', IncomeDay: 'THU', IncomeAmount: 300 },
  { VendorId: 'FREDS', IncomeDay: 'WED', IncomeAmount: 500 },
  { VendorId: 'SPIKE', IncomeDay: 'SAT', IncomeAmount: 100 },
  { VendorId: 'FREDS', IncomeDay: 'SAT', IncomeAmount: 500 },
  { VendorId: 'FREDS', IncomeDay: 'THU', IncomeAmount: 800 },
  { VendorId: 'JOHNS', IncomeDay: 'TUE', IncomeAmount: 600 } ]


  var res = alasql('select * from ? pivot (AVG(IncomeAmount) for IncomeDay)', [data])

returns

TypeError: Cannot set property 'columnid' of undefined

Might be a silly error I'm making. I think documentation with this case would be very helpful.

[mathiasrw]

Hi @keyscores

You are not forgotten. I cant get my head around whats working and whats not. Im using http://sqlfiddle.com/#!3/7d09c/1 as example - but its not playing nice.

[lpgeiger]

I got the example above to work by studying another test you have. Try this: http://jsfiddle.net/auazvzpg/

However, I don't understand the need for the [] syntax on the property that will be aggregated.

var res = alasql('select VendorId, IncomeDay, [IncomeAmount] from ? pivot (AVG(IncomeAmount) for IncomeDay)', [data])

[mathiasrw]

Nice !

[IncomeAmount] is only needed if the column is a keyword - so no need to wrap it: http://jsfiddle.net/auazvzpg/1/

(or are you talking about the [data] part?)

[lpgeiger]

Ok thanks, I had just copied the other test, I wasn't sure what the [] in the SELECT section was doing. Thanks again!

[iamvitali]

I'm also getting the Uncaught (in promise) TypeError: Cannot set property 'columnid' of undefined error message. :/

[BigsaveCory]

Also getting Cannot set property 'columnid'.