PIVOT, SQL
From a manager's perspective, pivoting provides a considerably simpler view of data, truly transforming it into information.
This feature lets you rotate row data into columns in the query response to create pivoted or “crosstabbed” views of query results, i.e. provide developers with the ability to rotate row data into columns.
The syntax of PIVOT expression is defined as:
<pivoted_table> ::= table_source PIVOT <pivot clause> table_alias <pivot_clause> ::= ( aggregate_function ( vaule_column ) FOR pivot_column IN ( <column_list> )
Below you can see example of query with PIVOT:
WITH ProductSales(ProductID, OrderYear, OrderTotal) AS ( SELECT det.productID, YEAR(hdr.orderdate), det.linetotal FROM sales.salesorderdetail det JOIN sales.salesorderheader hdr ON det.salesorderid = hdr.salesorderid ) -------- SELECT ProductSalesPivot.productID, Total_Sales_2001 = ISNULL([2001], 0), Total_Sales_2002 = ISNULL([2002], 0), Total_Sales_2003 = ISNULL([2003], 0), Total_Sales_2004 = ISNULL([2004], 0) FROM ProductSales PIVOT ( SUM(OrderTotal) FOR OrderYear IN ([2001], [2002], [2003], [2004]) ) AS ProductSalesPivot ORDER BY ProductSalesPivot.ProductID
Release Information
Valentina 2.5. This feature is supported in this release of Valentina and higher.