Contents
In data processing, a pivot table can automatically sort, count, total or give the average of the data from a table, displaying the results in a second table showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross tabulations. LiveView supports pivot queries to create pivot tables.
In LiveView, you can select a column in the source table as a pivot column and create columns in a destination table for each value in the source pivot column.
The following LiveView clients support pivot queries:
LiveView Web 1.2 and higher |
JavaScript Client API |
Java Client API |
lv-client |
In the following example, the pivot operation converts different values in the column year
to columns in the destination table. An aggregate expression then evaluates the data of every pivot column data point for
each group. Next, a summation on the SalesAmount
is performed in the following CountrySales
table in LiveView for the years 2005, 2006, 2007, and 2008.
The GROUP BY operation is performed on some columns. In this example, GROUP BY is performed on the column, Country
.
Consider the following LiveView query:
Select * from CountrySales PIVOT sum(SalesAmount) FOR Year VALUES [2005, 2006, 2007, 2008] GROUP BY Country
Once pivoted, the destination table looks like this:
The above example shows a basic pivot operation. LiveView also supports complex pivot table functionality, including:
- Multiple aggregate expressions for each pivot column value
-
Instead of using only a sum(salesamount), you can configure
m
expressions such as sum(salesamount), max(salesamount), and avg(salesamount). Each expression is evaluated for each year value in the VALUES clause (2005, 2006, 2007, and 2008). The total number of columns is derived from the number of aggregate expressions, multiplied by the total number of values in the VALUES clause. For example:Select * from CountrySales PIVOT sum(SalesAmount), max(SalesAmount), avg(SalesAmount) FOR Year VALUES [2005, 2006, 2007, 2008] GROUP BY Country
- Multiple pivot operations
-
LiveView pivot tables support
n
value expressions that use multiple columns as pivot columns, unlike the static use of single pivot column values such asYear
as shown in the example above.Select * from CountrySales PIVOT sum(SalesAmount), max(SalesAmount), avg(SalesAmount) VALUES [salesAmount>200.0, Year=2005, Year=2006, Year=2007, Year=2008, someOtherFieldInTable = 'someValue'] GROUP BY Country
For these complex pivot options, the total number of columns is ( (m*n) + any GROUP BY columns ).
When there are m
number of aggregate expressions in the PIVOT clause and n
number of expressions in the VALUES clause from the pivot column, the total number of resultant columns is m*n
. Each aggregate expression is computed for each pivot value from the VALUES clause. The column name is formed by concatenating
the aggregate expression's alias (first part below) and values expression's alias (second part below) and generating the result.
The symbol used for concatenation is '_
'.
- First part of column header (from the pivot aggregate expression)
-
If aggregate expressions are aliased, LiveView uses that alias to become the first part of column header in the result set.
If pivot aggregate expressions are not aliased, LiveView uses
expr_0, expr_1, expr_2...
in increments for subsequent aggregate expressions. For example:PIVOT sum(salesAmount) as total, max(salesAmount) 1) sum(salesAmount) as total -> total 2) max(salesAmount) -> expr_0
- Second part of column header (from the value expressions):
-
If the value expression is aliased, LiveView uses the alias name.
If the value expression is not aliased, LiveView uses the pattern
_incrementingCounter(_0, _1, _2)
. If the value is a literal, LiveView generates a valid column header by replacing all unacceptable characters to '_
'.If the the value is a number, LiveView prepends an underscore '
_
'.For example:
FOR YEAR VALUES [2015, YEAR=2016, 2017 as seventeen, noyear] 1) 2015 -> _2015 (forms a valid alias part, after prep-ending a '_') 2) YEAR = 2016 -> _0 (_incrementingCounter pattern) 3) 2017 as seventeen -> seventeen (alias provided) 4) noyear -> noyear ('noyear' the pivot value is a valid column header)
After LiveView generates the first part's alias of the column header from the aggregate expression and the second part's alias
from the pivot value, LiveView concatenates the two parts: the first part + '_
' + the second part.
For example:
PIVOT sum(salesAmount) as total, max(salesAmount) FOR YEAR VALUES [2015, YEAR=2016, 2017 as seventeen, noyear, YEAR=2018]
The Result schema will have the following column headers:
total_2015, total_0, total_seventeen, total_noyear, total_1, expr_0_2015, expr_0_0, expr_0_seventeen, expr_0_noyear, expr_0_1 total_2015 ----> total + '_' + _2015(if there is more than one '_' symbol while joining valid part names, they are considered as one) total_0 ----> total + '_' + _0(first value expression without alias for the pivot expression) total_seventeen ----> total + '_' + seventeen total_noyear ----> total + '_' + noyear total_1 ----> total + '_' + _1(second value expression without alias for the pivot expression) expr_0_2015 ----> expr_0 + '_' + _2015 expr_0_0 ----> expr_0 + '_' + _0(first value expression without alias for the pivot expression) expr_0_seventeen ----> expr_0 + '_' + seventeen expr_0_noyear ----> expr_0 + '_' + noyear expr_0_1 ----> expr_0 + '_' + _1(second value expression without alias for the pivot expression)
Consider the following example to understand pivot queries.
SELECT * from CountrySales PIVOT sum(SalesAmount), max(SalesAmount), avg(SalesAmount) FOR Year VALUES [2005, 2006, Year=2007, 2008, someOtherFieldInTable = 'someValue'] GROUP BY Country
Breaking down the above LiveQL pivot query syntax to understand its components:
SELECT *
Only the wildcard '*
' is allowed in the projection, which implies a combination of GROUP BY fields and values from the pivot column that satisfy
expressions in the VALUES clause.
FROM [TableName]
LiveView base table name. In the example above, TableName = Country
.
PIVOT sum(SalesAmount), max(SalesAmount), avg(SalesAmount) FOR Year VALUES [2005, 2006, Year=2007, 2008, someOtherFieldInTable = 'someValue']
The pivot operation contains three parts:
-
PIVOT: List of valid StreamBase aggregate expressions. In this example,
sum
,max
, andavg
. -
FOR: Column to be pivoted. This clause is optional if all the expressions inside
VALUES
clause are valid StreamBase test expressions and none of them are literals. In this example,Year
. -
VALUES: The values are valid StreamBase test expressions or literals. If the clause has literals, the
FOR
clause becomes mandatory. In the following example, the literal 2007 is interpreted asYEAR=2007
. Thus all literals are converted to expressions aspivotColumnMentionedInFOR=literalValue
and theVALUES
clause is treated as a collection of Boolean expressions.
GROUP BY [field].
In this example, GROUP BY base table name, Country
. See LiveQL Reference for more information regarding LiveQL syntax.
The following are additional examples to understand user input and the resulting schemas.
Example 1.
SELECT * FROM Sales PIVOT sum(SALES) AS TOTAL, max(SALES) AS HIGHEST FOR YEAR VALUES [ YEAR<=2014 AS OLD, 2015, 2016] GROUP BY SALESPERSON;
Result schema: SALESPERSON, TOTAL_OLD, TOTAL_2015, TOTAL_2016, HIGHEST_OLD, HIGHEST_2015, HIGHEST_2016
Example 2.
SELECT * FROM Sales PIVOT sum(SALES) AS TOTAL, max(SALES) AS HIGHEST VALUES [ YEAR=2014, YEAR=2015, YEAR=2016] GROUP BY SALESPERSON;
Result schema: SALESPERSON, TOTAL_0, TOTAL_1, TOTAL_2 HIGHEST_0, HIGHEST_1, HIGHEST_2
Example 3.
SELECT * FROM Sales PIVOT sum(if(curr='EURO') then AMT*1.2 else AMT) FOR YEAR VALUES [ YEAR<=2014 AS OLD, 2015, 2016] GROUP BY SALESPERSON;
Result schema: SALESPERSON, expr_0_OLD, expr_0_2015, expr_0_2016