Reshaping Data Using Custom SQL Connection

Version 2

    Description:

    Many times it is necessary to reshape your data to produce the visualization or analysis you need. If you're working with Excel files here's a handy tool that will that will make this job easy. However, you may find it beneficial to reshape your data "on the fly". You can use the following Custom SQL to do just that.

     

    Example:

     

    Here is the SQL for a single table connection:

    SELECT ['Region Sales$'].[Division] AS [Division],

      ['Region Sales$'].[Duracell] AS [Duracell],

      ['Region Sales$'].[Duracell LY] AS [Duracell LY],

      ['Region Sales$'].[Energizer] AS [Energizer],

      ['Region Sales$'].[Energizer LY] AS [Energizer LY],

      ['Region Sales$'].[Region] AS [Region]

    FROM ['Region Sales$']

     

    With Duracell and Energizer as separate fields some analysis will be difficult. This is a classic example of why reshaping the data can be necessary. Here's the Custom SQL that reshapes the data into a more useful form:

     

    SELECT

       ['Region Sales$'].[Division] AS [Division]

      ,['Region Sales$'].[Region] AS [Region]

      ,"Duracell" AS [Battery Type]

      ,['Region Sales$'].[Duracell] AS [Sales TY]

      ,['Region Sales$'].[Duracell LY] AS [Sales LY]

    FROM ['Region Sales$']

    UNION

     

    SELECT

       ['Region Sales$'].[Division] AS [Division]

      ,['Region Sales$'].[Region] AS [Region]

      ,"Energiizer" AS [Battery Type]

      ,['Region Sales$'].[Energizer] AS [Sales TY]

      ,['Region Sales$'].[Energizer LY] AS [Sales LY]

    FROM ['Region Sales$']

     

    Note:

         1) Energizer and Duracell are now in two different tables,

         2) Both companies now share two shared fields ([Sales TY] & [Sales LY])

         3) Now there is a new field (Battery Type) that can be used to differentiated

     

    [Note: This example was taken from a talk Ashley Ohmann's gave to the ATUG in June. (The PowerPoint and Excel workbooks are included in Andy Piper's note).