Switch to: V12V11V10V9V8V7V6V5

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.