Pivoting in SQL is a powerful technique that allows you to transform your data from a wide format to a long format or vice versa. It's particularly useful when you want to convert your data into a format that's easier to analyze or visualize. In this article, we'll explore SQL pivot examples to help you understand and apply this transformative feature.
Before diving into the pivot examples, let's understand the basic syntax of the PIVOT function in SQL. The PIVOT function takes three main arguments: a column to aggregate (the values), a column to pivot (the row labels), and a column to group the data by (the column labels).
Pivoting Data into a Horizontal Format
The most common use of the SQL PIVOT function is to transform data from a long format to a wide format. This is particularly useful when you have categorical data and you want to see the distribution of each category.
Consider the following sample data in a table named `Sales`: | Customer | Month | Sales | |----------|-------|-------| | A | Jan | 1000 | | B | Jan | 1200 | | A | Feb | 1500 | | C | Jan | 900 | | B | Feb | 1300 |
Pivot by Month
To pivot the data into a wide format by month, you can use the following SQL query:
SELECT Customer, [Jan], [Feb] FROM Sales PIVOT (SUM(Sales) FOR Month IN (['Jan'], ['Feb']));
The output will be: | Customer | Jan | Feb | |----------|-------|-------| | A | 1000 | 1500 | | B | 1200 | 1300 | | C | 900 | NULL |
Pivot by Both Month and Year
To pivot the data by both month and year, you first need to ensure your data is in the correct format. For instance, you might need to transform the `Month` column into a combined `YearMonth` column. Then, you can use the following query:
SELECT Customer, [Jan 2022], [Feb 2022] FROM Sales PIVOT (SUM(Sales) FOR YearMonth IN (['Jan 2022'], ['Feb 2022']));
The output will have `YearMonth` as column labels, allowing you to compare sales across multiple years.
Using Conditional Aggregation in PIVOT
While the PIVOT function is typically used with aggregates like SUM, you can also use other aggregate functions like AVG, MIN, or MAX, along with conditions to perform complex calculations.
For example, to find the maximum sales amount for each customer in each month, you can use the `MAX` function together with `CASE` statements in the PIVOT query:
SELECT Customer, [Jan], [Feb] FROM (SELECT Customer, Month, Sales, RANK() OVER (PARTITION BY Customer, Month ORDER BY Sales DESC) as rnk FROM Sales) t PIVOT (MAX(CASE WHEN rnk = 1 THEN Sales END) FOR Month IN (['Jan'], ['Feb']));
The output will showcase the maximum sales amount for each customer in each month.
Whether you're transforming your data into a different format for analysis, visualization, or just for better readability, SQL's PIVOT function offers a powerful tool. With practice and understanding of its syntax, you can gain significant insights from your data. So, go ahead and start exploring these SQL pivot examples in your own databases!