SQL Pivot

Pivot in SQL is a technique used to transform data from rows into columns. It allows you to take the data in your database and reshape it in a way that makes it easier to analyze and present. This operation rotates the data from rows into columns. This is useful when you have data that needs to be analyzed or presented in a different format than how the underlying tables are set up. Pivoting can be done using the PIVOT function, which is available in many popular database management systems such as Google BigQuery, SQL Server, and Oracle.

To perform pivoting in SQL, you need to have a table of data that you want to pivot. This table should have at least one column that you want to use as the row headings, one column that you want to use as the column headings, and one column that you want to use as the values.

Syntax

Example

Let's say you have a table of sales data that looks like this:

Table Total_Sales

To pivot this data so that it shows the sales for each store, with the years listed across the top, you can use the following SQL statement:

Result:

Let's break down the SQL statement:

The first subquery gets sales by store by year.

The PIVOT clause between the two subqueries specifies that we want to perform a pivot according to the subquery that follows.

The second subquery first specifies that we want to apply the SUM() function to the Sales column, as well as an alias. This alias is important beause it is used as the first part of the column name in the output. The second line in the second subquery then spells out exactly which years we want to view Sales by. In this case, we explicitly say that we want to look at Sales data for 2020, 2021, and 2022.

You can pivot data in many different ways, depending on your specific requirements. The key is to understand the structure of your data and how you want to transform it.