What is the usage of the SQL pivot function?

1 year ago

Jackson Davis

2 minutes

The PIVOT function in SQL is used to transform row data into column data. Essentially, it aggregates a query result containing multiple columns by specifying one or more columns, using their values as the names of new columns and filling those columns with the values of other columns.

The PIVOT function is typically used in conjunction with aggregate functions (such as SUM, COUNT, MAX, etc.) to summarize data. It is useful for situations where certain row data needs to be displayed as columns, for example summarizing sales data by different products and displaying the sales figures for each product as columns.

Here is the syntax for the PIVOT function:

SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
FROM <table>
PIVOT (
    <aggregation function>(<column to aggregate>)
    FOR <column to pivot>
    IN ([first pivoted value], [second pivoted value], ...)
) AS <alias>

In this case, is a column that does not require aggregation, [first pivoted column] and [second pivoted column] are specified columns whose values will be used as new column names.

is the table being queried, is the column to be aggregated, is the column that will be used as the new column names, and [first pivoted value] and [second pivoted value] are specific column values.

It’s important to note that the implementation of the PIVOT function may vary among different database systems, leading to differences in specific syntax details.