How to group pivot dynamic array in javascript or MySQL?

Question

Basically I want to pivot dynamic array, example this array I get from MySQL table:

[ { ruleAlias: 'DEMAND', date: '2019-03-22', count: 10 },
  { ruleAlias: 'VOUCHER', date: '2019-03-25', count: 20 },
  { ruleAlias: 'DEMAND', date: '2019-03-25', count: 10 },
  { ruleAlias: 'BILL', date: '2019-03-25', count: 2 },
  { ruleAlias: 'BILL', date: '2019-05-06', count: 1 } ]

I want this array became:

[ ['RULE', 'DEMAND', 'VOUCHER', 'BILL'],
  ['2019-03-22', 10, 0, 0],
  ['2019-03-25', 10, 20, 2],
  ['2019-05-06', 0, 0, 1]
]

and this is the query of MySQL I use:

SELECT ruleAlias, date, count
FROM (
    SELECT ruleAlias, DATE_FORMAT(createDate, '%Y-%m-%d') AS date, COUNT(DATE_FORMAT(createDate, '%Y-%m-%d')) AS count FROM histPoint WHERE ruleAlias IN (SELECT alias FROM mstRule WHERE clientId = 5) AND YEAR(createDate) = YEAR(CURDATE()) GROUP BY date, ruleAlias
) AS t

The data I get is dynamic, for example data above is 3 rule, but data from table can get more than 3 rule, how to pivot this kind of array to expected output in javascript or directly pivot in the sql?

Answer 1

You could take a hash table and collect all value for a given date/rule pair. Then build a table and use a default zero for not given pairs.

const key = (...a) => a.join('|');

var data = [{ ruleAlias: 'DEMAND', date: '2019-03-22', count: 10 }, { ruleAlias: 'VOUCHER', date: '2019-03-25', count: 20 }, { ruleAlias: 'DEMAND', date: '2019-03-25', count: 10 }, { ruleAlias: 'BILL', date: '2019-03-25', count: 2 }, { ruleAlias: 'BILL', date: '2019-05-06', count: 1 }],
    temp = {},
    result,
    dates = new Set,
    rules = new Set;

data.forEach(({ ruleAlias, date, count }) => {
    dates.add(date);
    rules.add(ruleAlias);
    temp[key(date, ruleAlias)] = (temp[key(date, ruleAlias)] || 0) + count;
});    

result = [
    ['RULE', ...rules],
    ...Array.from(dates, date => [
        date,
         ...Array.from(rules, rule => temp[key(date, rule)] || 0)
    ])
];

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }