S02029

Error Code

S02029

Error Message

Row reduction operation <xxx> cannot be used with PIVOT BY. RefId:S02029

Probable Causes

For a pivot table, each cell holds a single value aggregated from multiple groups. Therefore, using binary row-based functions (e.g., rowAvg, rowStd, rowVar) will throw an error.

For example:

arr = 1..10
id = take(1..5,10)
type1 = take(1..2, 10)
type2 = take(1..3, 10)

t = table(arr, id, type1, type2)

select rowAvg(arr, id) from t pivot by type1, type2;
select rowStd(arr, id) from t pivot by type1, type2;
select rowVar(arr, id) from t pivot by type1, type2;

Solutions

Avoid using the binary row-based functions when querying with a PIVOT BY clause.