Pivot on multiple columns #850
Description
I am new to Alasql, I wonder how can I create a PIVOT and show two aggregation. In below example how can I show AVG of values and SUM of price for each name and security. Also how can I give alias name for each aggregation i.e.
`
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
<script type="text/javascript" src="https://rawgit.com/agershun/alasql/develop/dist/alasql.js"></script>
</head>
<body>
<xmp id="output"></xmp>
<script type="text/javascript">
var data = [
{ security: 'Preferred Stock', name: 'Robert', value: 5, price:10 , date: '2014-1-3' },
{ security: 'Preferred Stock', name: 'Robert', value: 5, price:10 , date: '2014-1-5' },
{ security: 'Common Stock', name: 'Bert', value: 20, price:10 , date: '2014-1-6' },
{ security: 'Preferred Stock', name: 'Elizabeth', value: 10, price:10 , date: '2014-1-6' },
{ security: 'Common Stock', name: 'Robert', value: 20, price:10 , date: '2014-1-9' },
{ security: 'Preferred Stock', name: 'Bert', value: 20, price:10 , date: '2014-1-11' },
{ security: 'Preferred Stock', name: 'Robert', value: 5, price:10 , date: '2014-1-12' },
{ security: 'Preferred Stock', name: 'Robert', value: 15, price:10 , date: '2014-1-12' },
{ security: 'Options', name: 'Bert', value: 10, price:10 , date: '2014-1-13' },
{ security: 'Preferred Stock', name: 'Robert', value: 5, price:10 , date: '2014-1-14' },
{ security: 'Options', name: 'Robert', value: 15, price:10 , date: '2014-1-17' }
];
var res = alasql('SELECT name, "Series1" AS SeriesName, security, [value] \
FROM ? PIVOT (AVG([value]) FOR security)',[data]);
print( res );
var res1 = alasql('SELECT name, "Series2" AS SeriesName, security, price \
FROM ? PIVOT (SUM([price]) FOR security)',[data]);
print( res1 );
function print(x){
document.getElementById('output').textContent += JSON.stringify(x, null, '\t')+"\n";
}
</script>
</body>
</html>
With above code I am getting below result.
[
{
"name": "Robert",
"SeriesName": "Series1",
"Preferred Stock": 7,
"Common Stock": 20,
"Options": 15
},
{
"name": "Bert",
"SeriesName": "Series1",
"Common Stock": 20,
"Preferred Stock": 20,
"Options": 10
},
{
"name": "Elizabeth",
"SeriesName": "Series1",
"Preferred Stock": 10
}
]
[
{
"name": "Robert",
"SeriesName": "Series2",
"Preferred Stock": 50,
"Common Stock": 10,
"Options": 10
},
{
"name": "Bert",
"SeriesName": "Series2",
"Common Stock": 10,
"Preferred Stock": 10,
"Options": 10
},
{
"name": "Elizabeth",
"SeriesName": "Series2",
"Preferred Stock": 10
}
]
`
How to get below result. Thank you in advance
[
{
"name": "Robert",
"Series1 - Preferred Stock": 7,
"Series1 - Common Stock": 20,
"Series1 - Options": 15
"Series2 - Preferred Stock": 50,
"Series2 - Common Stock": 10,
"Series2 - Options": 10
},
{
"name": "Bert",
"Series1 - Common Stock": 20,
"Series1 - Preferred Stock": 20,
"Series1 - Options": 10
"Series2 - Common Stock": 10,
"Series2 - Preferred Stock": 10,
"Series2 - Options": 10
},
{
"name": "Elizabeth",
"Series1 - Preferred Stock": 10
"Series2 - Preferred Stock": 10
}
]