How to make a pivot table in SQL

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.

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:

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:

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.