alasql pivot !

by bilbobaggins

HTML

<script src="https://rawgit.com/agershun/alasql/develop/dist/alasql.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.full.min.js"></script>
<xmp id="output"></xmp>

JavaScript

print('Running the function a second time will fail. See console for error ')

  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 VendorId, IncomeDay, IncomeAmount  from ? pivot (AVG(IncomeAmount) for IncomeDay)', [data])
 
 print( res )




function print(x){
   ...