416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Using PIVOT and UNPIVOT in Manual Virtual Tables

Hide navigation
RSS
Modified on Tue, 02 Apr 2013 10:36 AM

Overview

The PIVOT and UNPIVOT SQL functions are often used in Manual Virtual Tables to organize your data in a certain format. This may be necessary depending on the visualization you are trying to use on your Dashboard. This example is using Microsoft SQL Server as the Data Source and follows Microsoft SQL Syntax.

PIVOT Example

The PIVOT function is used to rotate your row data into columns (similar to Transpose in Excel). Suppose you have the following table named dbo.KPI:
Pivot Table.

Pivot Table.


If instead of viewing each KPI as a row, you would prefer to view the KPIs as separate columns, you can write a SQL Query that uses the PIVOT function. Create a Manual Virtual Table with the query below:

SELECT 'Actual' AS KPI, Sales, Revenue, Expenses FROM (SELECT KPI, Actual FROM KPI) AS SourceTable PIVOT ( AVG(Actual) FOR KPI IN (Sales, Revenue, Expenses) ) AS PivotTable

This query will then produce the result below:
Pivot Result.

Pivot Result.


If you were creating a Bar Chart, you would now be able to select "Sales", "Revenue" and "Expenses" as the Measure.

UNPIVOT Example

UNPIVOT does the reverse of the PIVOT function, by rotating columns into rows. This is often necessary when you want to utilize a Pie Chart. Suppose you have the following table named dbo.Components:
Unpivot Table.

Unpivot Table.


If instead of viewing each KPI as a column, you would prefer to view the KPIs as separate rows, you can write a SQL Query that uses the UNPIVOT function. Create a Manual Virtual Table with the query below:

SELECT Component, Sales Amount FROM (SELECT Sales Component, TV, Laptops, Appliances FROM Components) G UNPIVOT (Sales Amount FOR Component IN (TV, Laptops, Appliances) ) AS UnPivotTable

This query will then produce the result below:
Unpivot Result.

Unpivot Result.


Note: In both PIVOT and UNPIVOT examples, it is possible to re-organize your raw data to avoid writing these queries. However, depending on the amount of data and the type of data source it may not be plausible to do so. It may be a relatively simple task in Excel, but in a relational database such as SQL Server, it would require the table to be redefined and repopulated.

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.