PIVOT

AnalytiX-BI supports the PIVOT relational operator to rotate the rows of a query by turning the unique values from one column in the query into multiple columns in the output and performing aggregations where required.

The AnalytiX-BI PIVOT has the following syntax:

PIVOT

  { aggregate_function }({ column_or_expression_to_aggregate })

FOR

  { pivot_column } IN ({ pivot_value [AS alias] } [,…n])

[ WHERE

  { where_predicates } ]

[ HAVING

  { having_predicates } ]

[ GROUP BY

  { group_by_columns } ]

[ ORDER BY

  { order_by_columns } ]

The AnalytiX-BI PIVOT works in the following way:

1.    All column references must be fully qualified.

2.    If multiple tables are referenced in the PIVOT query, it is required that a JOIN path exists between the tables and is configured in the data model via relationships; this is because AnalytiX-BI needs this information in order to build the underlying JOIN.

3.    The pivot values can be optionally aliased to change the names of the output columns.

4.    IF a WHERE clause is specified, it will be applied before the PIVOT operation is executed (this can used to pre-filter the source).

5.    If a GROUP BY clause is specified, PIVOT results will be grouped by the selected columns.

6.    The HAVING clause can be used to post-filter aggregated values in the pivoted columns. Pivoted columns must be qualified with the pivot column’s table name in the HAVING clause.

7.    The ORDER BY clause can be used to order the results of the pivot operation. If sorting on one of the pivoted columns, they must be qualified with the pivot column’s table name in the ORDER BY clause.

The following query retrieves the total of the quantities ordered for each product category grouped and sorted by calendar year.

PIVOT

  SUM(OrderDetails.Quantity)

FOR

  Categories.CategoryName IN ('Beverages', 'Condiments', 'Confections', 'Dairy Products', 'Grains/Cereals', 'Meat/Poultry', 'Produce', 'Seafood')

GROUP BY

  year(Orders.OrderDate) AS Year

ORDER BY

  year(Orders.OrderDate)

Expressions can be used for the aggregated values. The following query calculates the total amount ordered for products that are discontinued. The pivot values are aliased in order to rename the output columns.

PIVOT

  SUM(OrderDetails.Quantity * OrderDetails.UnitPrice * (1 - OrderDetails.Discount))

FOR

  Products.CategoryID IN (1 AS Beverages, 2 AS Condiments, 3 AS Confections, 4 AS [Dairy Products], 5 AS [Grains/Cereals], 6 AS [Meat/Poultry], 7 AS Produce, 8 AS Seafood)

WHERE

Products.Discontinued = true

The following query calculates the total amount ordered for years 1996, 1997 and 1998 and returns only the categories where the total sales per year where greater than 20,000.

PIVOT

  SUM(OrderDetails.Quantity * OrderDetails.UnitPrice * (1 - OrderDetails.Discount))

FOR

  Orders.[OrderDate.Year] IN ('1996', '1997', '1998')

HAVING

  Orders.[1996] > 20000  AND Orders.[1997] > 20000 AND Orders.[1998] > 20000

GROUP BY

  Categories.CategoryName