Q. How do I create a chart from pivoted data?

A. Use the UNPIVOT SQL statement to transform the pivoted data.

Note: UNPIVOT is not available in SQL Server 2000.

Consider a query that returns the following data set (produced using a query such as ‘SELECT * FROM FruitSales’):

SeriesName       Q1          Q2          Q3          Q4
---------------- ----------- ----------- ----------- ------
Blue Berries     2332        3456        4238        6754
Oranges          2938        4516        3456        8354

To use this data in a chart, it must be un-pivoted because the chart creates one data point per row with the x value/axis label categorized by a unique value in a specific column. These points are then created in series which are created from each unique value in a specific column.

Here's the data set after un-pivoting, where column values are transformed into row values:

SeriesName       CategoryName    Value
---------------- --------------- -----------
Blue Berries     Q1              2332
Blue Berries     Q2              3456
Blue Berries     Q3              4238
Blue Berries     Q4              6754
Oranges          Q1              2938
Oranges          Q2              4516
Oranges          Q3              3456
Oranges          Q4              8354

To produce the un-pivoted data, the original query must be wrapped with an outer query that uses UNPIVOT. Note the addition of two columns, CategoryName and Value, that are required by the UNPIVOT statement.

SELECT 
  SeriesName, CategoryName, Value  

FROM ( SELECT * FROM FruitSales ) AS FruitData

UNPIVOT ( Value FOR CategoryName IN (Q1,Q2,Q3,Q4) ) AS Data  

ORDER BY 
  SeriesName, Categoryname

Here's how the unpivoted data looks in Reporting Services:

Here's how to bind the unpivoted data looks in SharePoint:

Binding the data in ASP.NET and Windows Forms:

 
// Load un-pivoted data from SQL
System.Data.DataTable table = this.LoadDataFromSQL();

// Create a data view from table
System.Data.DataView view = new System.Data.DataView(table);

// Use DataBindCrossTab method to bind the data
Chart1.DataBindCrossTab(view, "SeriesName", "CategoryName", "Value", String.Empty);

And the final output: