Pivoting data between Excel and SQL Server
    • Dark
      Light
    • PDF

    Pivoting data between Excel and SQL Server

    • Dark
      Light
    • PDF

    Article Summary


    SQLSpreadsPremiumLogo.png
    Pivoting is available in SQL Spreads Premium and above


    From release 5.0 SQL Spreads can pivot data from rows in SQL Server into columns in Excel and save the data back to its unpivoted form into multiple rows in a SQL Server table.

    When working with pivoted data you can Update, Insert and Delete rows of data in the SQL Server table from the pivoted view.

    How to setup Pivoting in SQL Spreads

    To set up pivoting, open the SQL Spreads Designer and select your database table in the Database tab. Then switch to the new Pivot tab to set up the pivoting:

    Pivoting 1.png

    Select which database columns to use for pivoting

    Pivoting uses two database columns to specify how the rows in SQL Server are pivoted into columns in Excel; the Pivot Key column and the Pivot Value column.

    The Pivot Key column
    The PivotKey column is the database column that holds the identifiers used to pivot the rows into specific columns in Excel. A typical PivotKey is a month.

    Pivoting 2.png

    You select the Pivot Key column in the first drop down list in the Pivot tab:

    Pivoting 3.png

    The Pivot Value column
    The PivotValue column is the database column that holds the row values to be pivoted into columns in Excel:

    Pivoting 4.png

    You select the Pivot Value column in the second drop down list in the Pivot tab:

    Pivoting 5.png

    Setting up key columns when working in pivoted mode

    When working with pivoted data in SQL Spreads, a row-unique ID column (such as an Identity key) cannot be used as the key.

    Instead, a combination of database columns that together with the Pivot Key column uniquely identifies each row must be used. A typical setup is to use your business key columns as key columns in SQL Spreads.

    To simplify the setup, SQL Spreads will show a Key selection dialog after you have selected your Pivot Key and Pivot Value columns:

    Pivoting 6.png

    Select which database rows to show as columns in Excel

    When you select the Pivot Key column, SQL Spreads will fetch the unique values in the database for that column and show them in the list in the Pivot tab:

    Pivoting 8.png

    Each row in this list consists of two values separated by a comma;

    • The first text, before the comma, is the header text to show in Excel
    • The text after the comma is the Pivot key used to find the rows in the database table to pivot into this column.

    Pivoting 9.png

    Using columns with commas in the name

    If the Pivot key, or the Excel column name contains commas, replace the separator with a comma in brackets [,]
    image.png

    How data is transformed into the Pivoted view

    When a row does not exist in the database

    If there is no row in the database for a specific Pivot key, SQL Spreads will show NULL in the cell in Excel.

    Pivoting 10.png

    Grouping of data

    Normally, a database should only contain one row for each key combination.

    If a table contains multiple rows for one key combination, SQL Spreads will show the sum of the values in the multiple rows for numeric values, the latest date for dates and the longest text for text columns.

    Non-pivoted, non-key columns - eg a comment

    If you make a non-pivoted, non-key column editable, SQL Spreads will show you a warning dialog:

    Pivoting 11.png

    When you edit the data in one of these columns in Excel and save the data, SQL Spreads will update the column for all the unpivoted rows that exist in the database.

    Example:
    If you have a database table with a Comment column and make the Comment column editable in a Pivoted view in Excel, any updates that you do to the Comment column in Excel, will be saved into all unpivoted rows in the database table:
    Pivoting 12.png

    Pivoting FAQ

    Are there any limitations when working with data in a Pivoted view?

    Yes, there are a few limitations when working with data in pivoted mode:

    1. Conflict detection is not available.
    2. A worksheet with a Pivoted setup can only be configured using the SQL Spreads Designer and cannot be configured in the Advanced Setup.

    I get an "Ambiguous column..." error when using a Custom SQL Filter?

    When using the Pivoting in SQL Spreads together with a Custom SQL Filter, you may get a SQL error message saying "Ambiguous column name 'SQL Column'":
    image.png

    To avoid this error, please add the alias [Pivoted] before the column name mentioned in the error message, eg:

    [Pivoted].AccountKey
    

    The Custom SQL Filter will then look like this:
    image.png


    Was this article helpful?