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:
Select which database columns to use for pivoting
You select the Pivot Key column in the first drop down list in the Pivot tab:
You select the Pivot Value column in the second drop down list in the Pivot tab:
Setting up key columns when working in pivoted mode
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:
If you don’t see the key selection dialog, you can always set up the database key columns from the Columns tab in the SQL Spreads Designer:
Select which database rows to show as columns in Excel
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.
How data is transformed into the Pivoted view
When a row does not existing 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.
Grouping of data
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
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.
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:
Limitations when working with pivoted data
- Conflict detection is not available.
- A worksheet with a Pivoted setup can only be configured using the SQL Spreads designer and cannot be configured in the Advanced Setup.