Summary of the usage of PIVOT function in SQL

Preface

PIVOT is a feature in SQL Server that converts rows into columns. However, MySQL and Oracle do not directly support PIVOT syntax. However, you can use conditional aggregation or other techniques to simulate the behavior of PIVOT.

grammar:

-- Select data from subquery，Subquery selects the required data from the source table  
SELECT ...    
FROM     
   (  
      -- Source query，Select from the source table you want to performPIVOTOperation column  
      SELECT ...    
      FROM &lt;source_table&gt;  
      -- Can includeWHEREClause、GROUP BYClause等以筛选或组织数据  
   ) AS SourceTable    
  
-- PIVOToperate，Convert row data to column data  
PIVOT    
   (  
      -- Aggregation function，Used to calculate the value of each new column  
      aggregate_function(&lt;column_value&gt;)    
      -- Specify the column name to be converted  
      FOR &lt;column_name&gt;     
      -- Specify a list of names for new columns after conversion  
      IN ([first_pivoted_column], [second_pivoted_column], ...)  
   ) AS PivotTable;

Here is an example of how to implement PIVOT-like operations in SQL Server, MySQL, and Oracle.

1. SQL Server

Suppose you have a table named sales that contains three fields: year, product, and amount:

sql:

CREATE TABLE sales (  
    year INT,  
    product VARCHAR(50),  
    amount DECIMAL(10, 2)  
);  
  
INSERT INTO sales (year, product, amount) VALUES  
(2020, 'A', 100),  
(2020, 'B', 200),  
(2021, 'A', 150),  
(2021, 'B', 250);

You can use PIVOT to convert data:

SELECT *  
FROM (  
    SELECT year, product, amount  
    FROM sales  
) AS source_table  
PIVOT (  
    SUM(amount)  
    FOR product IN ([A], [B])  
) AS pivot_table;

This will return:

year	A	B
2020	100.0	200.0
2021	150.0	250.0

2. MySQL

In MySQL, you can use conditional aggregation to simulate PIVOT:

SELECT year,  
       SUM(CASE WHEN product = 'A' THEN amount ELSE 0 END) AS 'A',  
       SUM(CASE WHEN product = 'B' THEN amount ELSE 0 END) AS 'B'  
FROM sales  
GROUP BY year;

This returns the same result as SQL Server.

3. Oracle

In Oracle, you can also use conditional aggregation:

SELECT year,  
       SUM(CASE WHEN product = 'A' THEN amount ELSE 0 END) AS "A",  
       SUM(CASE WHEN product = 'B' THEN amount ELSE 0 END) AS "B"  
FROM sales  
GROUP BY year;

This returns the same results as SQL Server and MySQL.

Note that while the above queries logically simulate the behavior of PIVOT, they are not the true PIVOT syntax. If you need to convert on multiple columns or dynamic columns, you may need to build more complex queries or use stored procedures to generate SQL dynamically.

This is the end of this article about the usage of PIVOT functions in SQL. For more related SQL PIVOT functions, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!