Pivot and Unpivot in SQL || How to convert rows to Columns in sql

What is Pivot and Unpivot in SQL

In this article, we will learn about Pivoting and unpivoting data in SQL. But before how to do Pivot and Unpivot in SQL we must understand what is pivot and unpivot in SQL. In SQL Oracle, Pivot and Unpivot are relational operators that are used to transform one SQL table into another SQL  table in order to achieve a simpler view of the data. In simple terms, we can say that SQL Pivot operator converts the unique values from one column values which are in rows into different columns. Basically, it convert rows to columns in SQL. The SQL Unpivot operator does the opposite that is it transforms the different columns into rows identified by these unique columns name in a new column.

Pivot in SQL

Before going to on how SQL Pivot function works let’s first CREATE TABLE and then INSERT INTO data in that table. The Oracle Script I will be attaching in the last portion of the article.

In the first portion, we created a table PIVOT_TRAINING which will have four columns id, customer_id, product_code, and quantity. Click here to read more on how to create a table in ORACLE.

CREATE TABLE PIVOT_TRAINING (
  id            NUMBER,
  customer_id   NUMBER,
  product_code  VARCHAR2(5),
  quantity      NUMBER
);

In the second portion, we Insert value into SQL table by using the below oracle query. You can read more about how to use INSERT INTO statement from this link.

INSERT INTO PIVOT_TRAINING VALUES (1, 101, 'DA', 10);
INSERT INTO PIVOT_TRAINING VALUES (2, 101, 'DB', 20);
INSERT INTO PIVOT_TRAINING VALUES (3, 101, 'DC', 30);
INSERT INTO PIVOT_TRAINING VALUES (4, 102, 'DA', 40);
INSERT INTO PIVOT_TRAINING VALUES (5, 102, 'DC', 50);
INSERT INTO PIVOT_TRAINING VALUES (6, 103, 'DA', 60);
INSERT INTO PIVOT_TRAINING VALUES (7, 103, 'DB', 70);
INSERT INTO PIVOT_TRAINING VALUES (8, 103, 'DC', 80);
INSERT INTO PIVOT_TRAINING VALUES (9, 103, 'DD', 90);
INSERT INTO PIVOT_TRAINING VALUES (10, 104, 'DA', 100);
COMMIT;

In the third portion, we checked SQL data by using ORACLE SELECT STATEMENT. Learn more about SQL SELECT STATEMENT from here. From the above image, we can see that data has been inserted into the table so now we will start Pivoting data in SQL table.

SELECT * FROM
(select CUSTOMER_ID,PRODUCT_CODE,QUANTITY from PIVOT_TRAINING)
PIVOT ( SUM(QUANTITY) AS SUM_QUANTITY FOR  (PRODUCT_CODE) IN ( 'DA' DA,'DB' DB,'DC' DC,'DD' DD));

In the above SQL Query we took three columns customer_id,product_code, and quantity as we wanted to sum the quantity data by customer_id. After that, we used PIVOT and then mention quantity as sum(quantity) and also named it SUM_QUANTITY then we used FOR (PRODUCT_CODE) means we wanted to create columns for FORDUCT_CODE. In the final result, columns will be created with initials as unique product_id and it will concatenate with SUM_QUANTITY. Now after running this oracle query we will get the final result as illustrated in the below image.

Now in the above image, we can see that first column is customer_id against which new SQL columns have been created for different product_id. Also to be noted here that there are few NULL values available which means no values are available against this customer_id for this product_code. we can use different SQL aggregate functions to pivot SQL data.

Unpivot in SQL

Unlike PIBOT, the UNPIVOT operator converts column-based data into separate rows. To see the UNPIVOT operator in action we will create a new SQL table again.

From the above image, we can understand how my data is available. So we wanted the data which is available in columns in rows. Basically, we wanted to create a new table that should have product_id and quantity columns. It basically convert columns to rows in SQL. Now, let’s start our Oracle query as mentioned below.

SELECT *
FROM   UNPIVOT_TRAINING
UNPIVOT (QUANTITY FOR PRODUCT_CODE IN (PROUCT_CODE_AB AS 'AB',PROUCT_CODE_AC AS 'AC',PROUCT_CODE_AD AS 'AD',PROUCT_CODE_AE AS 'AE'));

In the above SQL code we can see we fetch all columns from the table then we use UNPIVOT. After that, we mentioned QUANTITY FOR PRODUCT_CODE which means we mentioned two columns names that we wanted to create. After that, we mentioned existing columns names and changed their names for ease of readability. These all product_code will be in rows now.

Now we can see the final result in the above image. Values now unpivot in SQL means two columns have been created e.g product_code and quantity and initial two SQL columns have been arranged accordingly.

You can download both SQL code for pivot function in SQL from below. Also, unpivot in SQL query is available below.