Useful SQL Patterns: Pivoting

As you start coding in SQL, you will use some statements and techniques over and over again. We call these “SQL patterns”. This series will look at the most common SQL patterns and consider how to use them.

The concept of pivot in SQL refers to taking the data in table rows and making that data into columns. This is very important in reporting, and it’s easy to do when you use the CASE statement. In fact, you will see that pivoting data is very similar to using CASE with SUM(). The idea is the same, but pivoting has a special place in our hearts because of its usefulness. It’s one of the most useful SQL patterns you’ll come across on your SQL path.

(Note: In this article, we will look at pivoting data using CASE rather than a PIVOT SQL clause. Some RDBMSs have a dedicated PIVOT command that functions like the method we present in this post. PIVOT command is only available in recent releases of the enterprise level RDBMSs, when the PIVOT is missing you can use this pattern. For more information on the PIVOT SQL command, check this out.)

To explain the idea behind PIVOT in SQL, let’s revisit the sample table we used for the CASE summarization pattern. It contains transactional data from a fictional small company.

The transactions table has these columns:

• id – A unique identifier for each transaction
• datetime – The timestamp for the transaction
• customer – The customer’s ID
• creditcard – The ID of the type of credit card used
• amount – The transaction amount, in dollars
• account – The customer’s account number
• type – The transaction type

We use this query to get some sample data:

SELECT
  id,
  datetime,
  customer,
  creditcard,
  amount,
  account,
  type
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
ORDER BY customer;

And the data is:

id	datetime	customer	creditcard	amount  	account	type
31	2017-05-01 00:00:00.000000	1	1	210	1	type_2
28	2017-02-01 00:00:00.000000	1	1	50	1	type_2
29	2017-03-01 00:00:00.000000	1	1	150	1	type_1
30	2017-04-01 00:00:00.000000	1	1	200	1	type_1
27	2017-01-01 00:00:00.000000	1	1	100	1	type_1
32	2017-01-01 00:00:00.000001	2	1	100	1	type_1
34	2017-03-01 00:00:00.000000	2	1	350	1	type_1
33	2017-02-01 00:00:00.000000	2	1	10	1	type_2
36	2017-05-01 00:00:00.000000	2	1	10	1	type_1
35	2017-04-01 00:00:00.000000	2	1	600	1	type_3
38	2017-04-01 00:00:00.000000	3	1	600	1	type_3
39	2017-05-01 00:00:00.000000	3	1	10	1	type_3
37	2017-01-01 00:00:00.000000	3	1	350	1	type_3

Understanding the Pivot SQL Pattern

Let’s now look at an example based on our transactional table data. We want to see the sum of all transactions from all customers divided by transactional types. As we have transactional type data in the type column, we will need to change the rows to columns. In this way, every new column will represent one data type.

The query with a PIVOT SQL clause is:

SELECT
  customer,
  SUM(
      CASE WHEN type = 'type_1'
        THEN amount
      ELSE 0
      END
  ) AS sum_type_1,
  SUM(
      CASE WHEN type = 'type_2'
        THEN amount
      ELSE 0
      END
  ) AS sum_type_2,
  SUM(
      CASE WHEN type = 'type_3'
        THEN amount
      ELSE 0
      END
  ) AS sum_type_3
FROM transactions
WHERE datetime BETWEEN TIMESTAMP '2017-01-01' AND '2017-05-01'
GROUP BY customer
ORDER BY customer ;

A look at the code reveals that we are using the CASE pattern: when the value in the type column (type_1) is the same as the value in the sum_type column (sum_type_1) the column’s value is added to the relevant sum_type column. When it is not, we put a “0” in the column.

And the result set looks like this:

customer	sum_type_1	sum_type_2	sum_type_3
1		450		260		0
2		460		10		600
3		0		0		960

Try It Yourself

In this case, we used transaction amounts as our pivot point. A similar query could count the number of particular types of transactions. You have the table definition in Vertabelo and the query illustrated in this post.

Try creating the counting query for yourself!

Did you miss our first post about useful SQL patterns? Read it here.