0.1.4 • Published 7 years ago
sqlpivot v0.1.4
sqlpivot
Pivot an array of objects in a similar way to ms-sql
Installation
npm install sqlpivot
Usage
Pivot(<Table>, <ValueColumn>, <KeyColumn>, <Aggregator> [, <Comparer>])
- Table: An array of objects
- ValueColumn: The name of the property on each of the above objects which will be used as the value.
- KeyColumn: The column around which values will be pivoted
- Aggregator: What to do with the values from multiple rows which will be squashed together - The order the rows will be passed to this function is not defined
- Comparer: (optional) how to determine that 2 rows should be squashed together. The default comparer uses the values for all other columns excluding the Value and Key columns, if any difference is found then a new row will be created.
const Pivot = require('sqlpivot');
let Table = [
{ Key: 'A', Value:1, Other:'X', Columns:'Y' },
{ Key: 'A', Value:2, Other:'X', Columns:'Y' },
{ Key: 'B', Value:3, Other:'X', Columns:'Y' },
{ Key: 'B', Value:4, Other:'X', Columns:'Y' },
{ Key: 'A', Value:5, Other:'Y', Columns:'X' }
];
let Sum = (acc,curr)=>(acc||0)+curr;
let Pivoted = Pivot(Table, 'Value', 'Key', Sum);
// Pivoted now looks like this:
// [
// { Other: 'X', Columns: 'Y', A: 3, B: 7 },
// { Other: 'Y', Columns: 'X', A: 5, B: null }
// ]
See Also
MSDN explains pivot better than I can!
Alternatives
The same thing can be achieved directly in most/all flavours of SQL with varying degrees of ease