sqlpivot

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