Posted on February 13, 2023 by Kevin Guisarde .

This blog post explores the applications of the Pivot and Unpivot data manipulation techniques within the context of the Oil and Gas industry. These powerful techniques are commonly used in data analytics and data science to summarize and transform data and can be especially useful for professionals in this industry who need to analyze large datasets. Through the use of SQL Server, the blog will demonstrate how these techniques can be effectively applied to the Oil and Gas industry to streamline data analysis and improve decision-making.

Agenda

1. Introduction to Pivot and Unpivot in SQL Server
2. Understanding the different concept types in Pivot and Unpivot
3. Real World Examples in the Oil and Gas Industry using SQL Server
4. Most Commonly Asked Interview Question in Pivot and Unpivot
5. Conclusion

Introduction to Pivot and Unpivot in SQL Server

Pivot and Unpivot are powerful data manipulation techniques used to summarize and transform data. These techniques are widely used in the data analytics and data science fields. In this blog, we will discuss how these techniques can be applied to the Oil and Gas industry using SQL Server.

Understanding the Different Concept Types in Pivot and Unpivot

Pivot

Pivot is used to transform data from a row-based format to a column-based format. It allows data to be summarized and grouped based on columns.

Example:
Consider the following data table that shows the sales of different products for different months.

CREATE TABLE Sales ( 
  Product varchar(50), 
  Month varchar(50), 
  Sales int 
);

INSERT INTO Sales (Product, Month, Sales) 
VALUES 
  ('Product A', 'January', 100), 
  ('Product A', 'February', 200), 
  ('Product A', 'March', 300), 
  ('Product B', 'January', 400), 
  ('Product B', 'February', 500), 
  ('Product B', 'March', 600); 

The data can be transformed using the PIVOT operator as follows:

SELECT * 
FROM 
  (SELECT Product, Month, Sales 
  FROM Sales) AS SourceTable 
PIVOT 
( 
  SUM(Sales) 
  FOR Month IN ([January], [February], [March]) 
) AS PivotTable; 

The result will be as follows:

Product | January | February | March 
--------------------------------------- 
Product A | 100   | 200   | 300 
Product B | 400   | 500   | 600 

Unpivot

Unpivot is used to transform data from a column-based format to a row-based format. It allows data to be summarized and grouped based on rows.

Example:
Consider the following data table that shows the sales of different products for different months.

CREATE TABLE Sales ( 
  Product varchar(50), 
  January int, 
  February int, 
  March int 
);

INSERT INTO Sales (Product, January, February, March) 
VALUES 
  ('Product A', 100, 200, 300), 
  ('Product B', 400, 500, 600); 

The data can be transformed using the UNPIVOT operator as follows:

SELECT Product, Month, Sales 
FROM 
  Sales 
UNPIVOT 
( 
  Sales FOR Month IN (January, February, March) 
) AS UnpivotTable; 

The result will be as follows:

Product | Month | Sales 
----------------------------- 
Product A | January | 100 
Product A | February | 200 
Product A | March | 300 
Product B | January | 400 
Product B | February | 500 
Product B | March | 600

Real-World Examples in the Oil & Gas Industry 

Using SQL Server:

CREATE TABLE OilProduction ( 
  Country varchar(50), 
  January int, 
  February int, 
  March int 
);

INSERT INTO OilProduction (Country, January, February, March) 
VALUES 
  ('USA', 100, 200, 300), 
  ('Saudi Arabia', 400, 500, 600), 
  ('Russia', 700, 800, 900); 

1. Display the total oil production of each country for the first quarter of the year (January to March).

SELECT Country, SUM(January + February + March) AS TotalProduction 
FROM OilProduction 
GROUP BY Country; 

2. Display the oil production of each country for each month.

SELECT Country, Month, Sales 
FROM 
  OilProduction 
UNPIVOT 
( 
  Sales FOR Month IN (January, February, March) 
) AS UnpivotTable; 

3. Display the oil production of each country in a column-based format.

SELECT * 
FROM 
  (SELECT Country, Month, Sales 
    FROM 
      (SELECT Country, January, February, March 
        FROM OilProduction) AS SourceTable 
    UNPIVOT 
    ( 
      Sales FOR Month IN (January, February, March) 
    ) AS UnpivotTable) AS FinalTable 
PIVOT 
( 
  SUM(Sales) 
  FOR Month IN ([January], [February], [March]) 
) AS PivotTable; 

Most Commonly Asked Interview Question in Pivot and Unpivot

Q: What is the difference between Pivot and Unpivot?

A: Pivot and Unpivot are data manipulation techniques used to summarize and transform data. The main difference between these techniques is the direction of transformation. Pivot is used to transform data from a row-based format to a column-based format. Unpivot is used to transform data from a column-based format to a row-based format.

I have used these techniques in a previous project where I was required to analyze the sales of different products for different months. I used Pivot to summarize the data and transform it into a column-based format. This allowed me to easily analyze the sales of each product for each month. I also used Unpivot to transform the data back into a row-based format so that I could perform further analysis.

Conclusion

In this blog, we discussed the basics of Pivot and Unpivot and how they can be applied to the Oil and Gas industry using SQL Server. We also looked at real-world examples and the most commonly asked interview questions in Pivot and Unpivot. These techniques are powerful tools for data manipulation and can be used to summarize and transform data in a variety of industries.