SQL, or Structured Query Language, is a standard language for managing and manipulating relational databases. One of its powerful features is the ability to transpose data using a technique known as pivoting. SQL Server's PIVOT operator and Syrie's CROSS APPLY or APPLY can transform rows into columns, providing a more friendly and readable format for data analysis. Let's explore SQL pivot with an example from W3Schools.
W3Schools, a popular website for learning web development, offers an interactive tutorial on SQL pivot. In this tutorial, we'll transform a table containing sales data for different cities into a compact format using the PIVOT operator.
Before Pivot: sales data in rows
Consider the following sample **sales** table:
| City | Country | Sales |
|---|---|---|
| Seattle | USA | 150000 |
| Rio | Brazil | 200000 |
| Seattle | USA | 250000 |
| Cairo | Egypt | 100000 |
| Rio | Brazil | 150000 |
Before pivoting, the data is displayed with each row representing a sale in a specific city.
Pivoting cities into columns
Now let's transform the data to have cities in columns. Assume we want to sum sales by city. Here's the SQL code using the PIVOT operator:
```sql SELECT * FROM ( SELECT City, SUM(Sales) as TotalSales FROM sales GROUP BY City ) AS SourceTable PIVOT ( SUM(TotalSales) FOR City IN ([Seattle], [Rio], [Cairo]) ) AS PivotTable; ```
This code first groups the data by city and sums the sales. Then, it pivots this data into a table with cities as column headers.
Results: sales data in columns
Here's the resulting table after the pivot operation:
| Country | Seattle | Rio | Cairo |
|---|---|---|---|
| USA | 400000 | ||
| Brazil | 350000 | ||
| Egypt | 100000 |
Now, each column represents a city, with total sales aggregated for that city.
Transforming data with SQL Server's PIVOT: an extension
SQL Server's PIVOT operator is not available in all SQL flavors. In cases where PIVOT is not supported, such as MySQL, we can use the CROSS APPLY or APPLY operator with some creative query writing. However, this tutorial mainly focuses on SQL Server's PIVOT operator as demonstrated in the W3Schools example.
Understanding SQL pivot is essential for data professionals, enabling them to create clearer and more informative reports. By transforming data from rows to columns, pivoting offers a more intuitive and user-friendly data representation, especially when dealing with large datasets or complex queries.
Start exploring SQL pivot today and reshape your data to unlock new insights! Don't forget to bookmark W3Schools' SQL Pivot tutorial for handy reference and more practice examples.