SQL > Advanced 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

The syntax for SQL PIVOT is

[SQL Statement]
PIVOT
( Aggregate_Function(Column_1)
FOR COLUMN_2 IN (VALUE1, VALUE2, ...)
)

Example

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

Table Total_Sales

 Year  Store  Sales 
 2020  Chicago  100 
 2020  Phoenix  200 
 2020  London  250 
 2021  Chicago  200 
 2021  Phoenix  400 
 2021  London  550 
 2022  Chicago  300 
 2022  Phoenix  150 
 2022  London  220 

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:

SELECT * FROM
(
SELECT Store, Year, SUM(Sales) Sales
FROM Total_Sales
GROUP BY Store, Year
)
PIVOT
(
SUM(Sales) as Sales
FOR Year in (2020,2021,2022)
);

Result:

Store Sales_2020 Sales_2021 Sales_2022 
Chicago 100200300
Phoenix 200400150
London 250550220

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.

Next: SQL Window Functions

This page was last updated on October 11, 2024.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact