Back to articles list June 18, 2017 - 4 minutes read Useful SQL Patterns: Pivoting Aldo Zelen Aldo is a data architect with a passion for the cloud. From leading a team of data professionals to coding a data warehouse in the cloud, Aldo has experience with the whole lifecycle of data-intensive projects. Aldo spends his free time forecasting geopolitical events in forecasting tournaments. Tags: PIVOT SQL patterns transaction 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 the CASE summarization pattern, which you can read about here. 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. Tags: PIVOT SQL patterns transaction You may also like How to Use LIKE in SQL: SQL Pattern Matching SQL pattern matching is a very important and useful ability. In this article, we look at how you can match patterns using LIKE in SQL. Read more Useful SQL Patterns: Matching Nulls by Masking Nulls Today, in the first post of the SQL patterns series, we will consider the match by null pattern. It’ll help you deal with tables containing null values. Read more Useful SQL Patterns: Conditional Summarization with CASE When you are using SQL aggregate functions to create report queries, you'll frequently find yourself using conditional summarization with the CASE operator. Read more Useful SQL Patterns: Date Generator Do you have to manually add all the missing days? No. You can use the SQL pattern known as a date generator to fill in the gaps. Read more You Want to Learn SQL? You've Come to the Right Place! If you want to learn SQL basics or enhance your SQL skills, check out LearnSQL.com for a wide range of SQL courses and tracks. Read more What SQL Practice Do You Need to Prepare for a Technical Job Interview? Need some SQL practice before a technical job interview? Here are six ways to prepare for various types of database and SQL jobs! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.