SafeDrop LogoPivotSQL

Overview

PivotSQL uses a simple and intuitive YAML syntax to define complex pivot tables. This allows our engine to understand what kind of pivot table you want, so it can compile the SQL query for your flavor of database. The pivot definition syntax is simple enough that its whole can be displayed in the table below. You can click on attribute names to find out more about it.

Attribute
Explanation
table: string
*
select from this table
Measure_Name_1:
query: string
*
SQL of measure
Format numbers for this measure.
Define pivot table columns and rows.
rows:
- column_name_1
*
Column from table
columns:
- column_name_2
*
Column from table. Must have unique_values defined.
schema:
Provide information about the table
Existing_Column_1:
unique_values: [ value1, value2, ...]
Break down into these values. Must be defined if Existing_Column_1 is used in Breakdown columns.
+Calculated Column:
Use the plus sign (+) at the beginning to define a calculated column.
query: string
*
Define SQL for this column.

Measures

Easily define a measure, add formats, and use references to build complex measures.

To define a measure, simply add its name and the query you want to use. For example, we define a total_sales measure that is calculated as the sum of the sales column.

Define Pivot
table: icecream_sales
measures:
    total_sales:
        query: sum(sales)

That is one of the simpest example and isn't terribly interesting. In most reports, you'd want to have the numbers formatted properly. Instead of showing a sales of 1200.32274600, it's better to show as $1200.32. SQL can achieve that with somewhat complex string manipulations for such a "small" ask. With PivotSQL syntax, it is much more intuitive.

Measure Formats

To format the total_sales measure as currency, add a note "format: currency.2". The dot 2 means keeping 2 decimal places. Similarly, "currency.6" would mean 6 decimal places. If you say only "currency", it defaults to no decimal places.

You might be thinking that the example is showing MySQL syntax, it won't work in PostgreSQL, SQLite, or many other SQL flavors. The good news is , PivotSQL got that covered for you, in the App, on the top of the page you can select different flavors of SQL. The query will be compiled using that SQL flavor's syntax.

Define Pivot
table: icecream_sales
measures:
    total_sales:
        query: sum(sales)
        format: currency.2

The number after the dot indicates the how many decimal places to keep.

Format Type
Examples
Currency
currency
gives $1,234
currency.2
gives $1,234.56
Number
number
gives 1,234
number.5
gives 1,234.56789
Percentage
percentage
gives 12%
percentage.3
gives 12.345%

Measure references

You can build new measures based on other measures. Use a dollar sign to refer to other measures. This eliminates the need in pure SQL to manually duplicate the measure logic, which is prone to mistaks as we are humans after all. In the example below, we created a avg_price measure based on total_sales and total_units measures.

Define Pivot
table: icecream_sales
measures:
    total_sales:
        query: SUM(sales)
    num_products:
        query: COUNT(DISTINCT product_id)
    avg_sales_by_product:
        query: $total_sales / $num_products
        format: currency