To create a pivot table in SQL, you will need to use the GROUP BY and CASE statements. Here is an example of how you might go about creating a pivot table.
A Database Manager That Is Modern, Fast, & Easy To Use
Tried a few tools. Beekeeper was the only one that I found that felt right. Most had a very 1990's feel to them - Allan
I built Beekeeper Studio because, like Allan, I wanted something more intuitive and modern than all the existing clunky apps I could find. My customers agree - they love using Beekeeper and they tell me every day! Give it a try, I bet you'll like it too.
Using a demo sales table
First, let’s say we have a table called sales that contains information about sales transactions. The table has the following columns: date, product, quantity, and price.
Simple pivot table example
To create a pivot table that shows the total quantity and total price for each product, we would use the following query:
SELECT
product,
SUM(CASE WHEN quantity THEN quantity ELSE 0 END) AS total_quantity,
SUM(CASE WHEN price THEN price ELSE 0 END) AS total_price
FROM sales
GROUP BY product
This query uses a CASE statement to specify which values should be included in the pivot table, and then uses the SUM function to calculate the total quantity and total price for each product. The GROUP BY statement groups the rows by product, which allows us to see the totals for each product separately.
Pivot table results
Here is an example of what the resulting pivot table might look like:
| product | total_quantity | total_price |
|---|---|---|
| ProductA | 50 | 500 |
| ProductB | 100 | 1000 |
| ProductC | 75 | 750 |
SUM, AVG, or other aggregate functions
You can also use the GROUP BY and CASE statements to create pivot tables that show other types of data, such as the average price for each product. For example, the following query would create a pivot table that shows the average price for each product:
SELECT
product,
AVG(CASE WHEN price THEN price ELSE NULL END) AS avg_price
FROM sales
GROUP BY product
The resulting pivot table would look something like this:
| product | avg_price |
|---|---|
| ProductA | 10 |
| ProductB | 20 |
| ProductC | 15 |
Summary
In summary, to create a pivot table in SQL, you can use the GROUP BY and CASE statements along with aggregation functions like SUM and AVG to calculate and display the data you want to see in the pivot table. This approach allows you to easily summarize and analyze large amounts of data, and can be very useful for making business decisions.
