Cách sử dụng SQL PIVOT

Câu trả lời nhanh: Cách Pivot trong SQL

Toán tử PIVOT của SQL Server hữu ích khi tóm tắt dữ liệu vì nó cho phép chuyển các hàng thành cột. Hãy xem bảng city_sales dưới đây, bảng này hiển thị doanh số chung của một sản phẩm tại năm thành phố lớn của Mỹ.

Ví dụ về bảng cần chuyển đổi bằng SQL PIVOT. Hình ảnh của Tác giả.

Chúng ta sẽ dùng truy vấn sau, sử dụng toán tử PIVOT, để pivot nhiều cột trong bảng trên.

-- Select the columns for the output: city and sales data for 2019, 2020, and 2021
SELECT 
    city,
    [2019] AS Sales_2019,
    [2020] AS Sales_2020,
    [2021] AS Sales_2021
FROM
    (
        -- Subquery to select city, year, and sales from city_sales table
        SELECT city, year, sales 
        FROM city_sales
    ) AS src
PIVOT
(
    -- Pivot the sales data to have years as columns and sum the sales for each year
    SUM(sales)
    FOR year IN ([2019], [2020], [2021])
) AS pvt;

Ví dụ kết quả chuyển đổi bằng SQL PIVOT. Hình ảnh của Tác giả.

PIVOT trong SQL là gì

Pivoting là một kỹ thuật trong SQL dùng để chuyển hàng thành cột trong dữ liệu dạng bảng. Trong SQL Server và Oracle, pivoting được thực hiện với toán tử PIVOT. Cú pháp của toán tử PIVOT dưới đây có ba phần chính:

• SELECT: Câu lệnh SELECT tham chiếu các cột sẽ trả về trong bảng tổng hợp SQL.

• Truy vấn con: Truy vấn con chứa nguồn dữ liệu hoặc bảng sẽ được đưa vào bảng tổng hợp SQL.

• PIVOT: Toán tử PIVOT chứa các phép tổng hợp và bộ lọc sẽ áp dụng trong bảng tổng hợp.

-- Select the non-pivoted column and the pivoted columns with aliases
SELECT 
    [non-pivoted column],               
    [first pivoted column] AS [column name],   
    [second pivoted column] AS [column name],  
    ...
FROM
    (
        -- Subquery to select the necessary columns from the source table
        SELECT [columns] 
        FROM [source_table]
    ) AS source_table
PIVOT
(
    -- Pivot operation to aggregate data and transform rows into columns
    [aggregate_function]([pivot_column])        
    FOR [pivot_column] IN ([first pivoted column], [second pivoted column], ...) 
) AS pivot_table; -- Alias for the result of the pivot operation

Các cách triển khai PIVOT theo từng hệ quản trị cơ sở dữ liệu

Cơ sở dữ liệu SQL Server và Oracle hỗ trợ trực tiếp toán tử PIVOT. Tuy nhiên, MySQL và PostgreSQL có các phương pháp thay thế để tạo bảng tổng hợp trong SQL.

PIVOT trong SQL Server

SQL Server hỗ trợ nguyên bản toán tử PIVOT. Ở đây, chúng ta sẽ dùng toán tử PIVOT để chuyển hàng thành cột và tóm tắt dữ liệu bằng các hàm tổng hợp như SUM(). Chúng ta cũng sẽ dùng các mệnh đề SQL như WHERE, GROUP BY và ORDER BY để tinh chỉnh thao tác dữ liệu.

Ví dụ dưới đây minh họa cách dùng toán tử PIVOT để lọc dữ liệu cho năm 2020 trở đi (WHERE), nhóm dữ liệu theo thành phố và năm (GROUP BY), và sắp xếp theo thành phố (ORDER BY):

-- Select the city and sales data for the years 2019, 2020, and 2021
SELECT 
    city,                         
    [2019] AS Sales_2019,             
    [2020] AS Sales_2020,           
    [2021] AS Sales_2021              
FROM
    (
        -- Subquery to select city, year, and sales from the city_sales table
        SELECT city, year, sales 
        FROM city_sales
        WHERE year >= 2020 -- filtering
        GROUP BY city, year, sales -- grouping
    ) AS src                   
PIVOT
(
    -- Pivot the sales data to have years as columns, averaging the sales over each year
    SUM(sales) -- aggregating
    FOR year IN ([2019], [2020], [2021]) 
) AS pvt;         

Ví dụ đầu ra của bảng đã chuyển đổi bằng SQL PIVOT với các mệnh đề thông dụng. Hình ảnh của Tác giả.

PIVOT trong Oracle

Tương tự SQL Server, Oracle cũng dùng toán tử PIVOT để chuyển hàng thành cột. Tuy nhiên, cú pháp của toán tử PIVOT trong Oracle hơi khác so với SQL Server. Truy vấn dưới đây cho thấy cách xuất hiện của toán tử PIVOT trong Oracle. Lưu ý rằng các cột được đặt bí danh ngay trong toán tử PIVOT, khác với câu lệnh SELECT bên ngoài trong SQL Server.

-- Outer SELECT to choose all columns resulting from the PIVOT operation
SELECT *
FROM (
    -- Inner SELECT to retrieve the raw data of city, year, and sales
    SELECT city, year, sales
    FROM sales
)
-- PIVOT operation to convert rows to columns
PIVOT (
    SUM(sales) 
    -- Specify the year values to pivot and alias them as Sales_<year>
    FOR year IN (2019 AS Sales_2019, 2020 AS Sales_2020, 2021 AS Sales_2021)
)
ORDER BY city;

Pivoting trong MySQL

Cơ sở dữ liệu MySQL không hỗ trợ toán tử SQL PIVOT. Để tạo bảng tổng hợp trong MySQL, bạn phải dùng câu lệnh CASE kết hợp với tổng hợp có điều kiện. Ví dụ, truy vấn dưới đây sẽ tạo một bảng tổng hợp để cộng gộp dữ liệu theo tổng doanh số của các năm khác nhau, được nhóm và sắp xếp theo city.

-- Select the city and sum the sales data for the years 2019, 2020, and 2021
SELECT 
    city,
    SUM(CASE WHEN year = 2019 THEN sales ELSE 0 END) AS Sales_2019,
    SUM(CASE WHEN year = 2020 THEN sales ELSE 0 END) AS Sales_2020,
    SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS Sales_2021
FROM 
    city_sales 
GROUP BY 
    city
ORDER BY 
    city; 

Pivoting trong PostgreSQL

Cơ sở dữ liệu PostgreSQL cũng không hỗ trợ toán tử SQL PIVOT. Do đó, khi tạo bảng tổng hợp, bạn cần dùng câu lệnh CASE với tổng hợp có điều kiện. Truy vấn dưới đây là ví dụ về các câu lệnh CASE có điều kiện dùng để tạo bảng tổng hợp trong PostgreSQL.

-- Select the city and sum the sales data for the years 2019, 2020, and 2021
SELECT 
    city,
    SUM(CASE WHEN year = 2019 THEN sales ELSE 0 END) AS Sales_2019, 
    SUM(CASE WHEN year = 2020 THEN sales ELSE 0 END) AS Sales_2020,
    SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS Sales_2021
FROM 
    city_sales
GROUP BY 
    city
ORDER BY 
    city; 

Kỹ thuật nâng cao với SQL PIVOT

Có một số kỹ thuật pivot nâng cao trong SQL để viết các truy vấn phức tạp. Ở phần này, chúng ta sẽ xem xét pivot động (dynamic), được dùng để tạo truy vấn cho các bảng tổng hợp khi các cột cần pivot chưa biết trước. Phương pháp này dùng SQL để tạo bảng tổng hợp tại thời điểm chạy.

PIVOT động trong SQL Server

Truy vấn dưới đây dùng PIVOT để pivot động cột year trong SQL Server. Truy vấn sẽ truy xuất các năm khác nhau từ bảng city_sales. Sau đó sẽ xây dựng và thực thi một truy vấn PIVOT động bằng các năm đã lấy được.

-- Declare variables to hold the column names and the dynamic query
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);
-- Get distinct values of the year column and concatenate them into a string
SELECT @cols = STRING_AGG(QUOTENAME(year), ',') 
               FROM (SELECT DISTINCT year FROM city_sales) AS years;
-- Construct the dynamic PIVOT query
SET @query = '
SELECT city, ' + @cols + '
FROM
(
    -- Subquery to select city, year, and sales from the city_sales table
    SELECT city, year, sales
    FROM city_sales
) AS src
PIVOT
(
    -- Pivot the sales data to have years as columns, summing the sales for each year
    SUM(sales)
    FOR year IN (' + @cols + ')
) AS pvt
ORDER BY city';  -- Order the results by city
-- Execute the dynamic PIVOT query
EXEC sp_executesql @query;

Ví dụ kết quả bảng sử dụng SQL dynamic PIVOT. Hình ảnh của Tác giả.

PIVOT động trong Oracle

Trong cơ sở dữ liệu Oracle, pivot động được hỗ trợ bằng cách thực thi truy vấn động với câu lệnh EXECUTE IMMEDIATE. Hàm LISTAGG cũng được dùng để tổng hợp động tên cột và dấu nháy đơn ' ' dùng trong các bí danh bên trong pivot.

DECLARE
    cols VARCHAR2(4000);
    sql_query VARCHAR2(4000);
BEGIN
    -- Get the list of years dynamically
    SELECT LISTAGG('''' || year || ''' AS ' || 'sales_' || year, ',') 
    INTO cols
    FROM (SELECT DISTINCT year FROM city_sales);
    -- Construct the dynamic SQL query
    sql_query := 'SELECT * FROM (
                      SELECT city, year, sales
                      FROM city_sales
                  )
                  PIVOT (
                      SUM(sales)
                      FOR year IN (' || cols || ')
                  )
                  ORDER BY city';
    -- Execute the dynamic SQL query
    EXECUTE IMMEDIATE sql_query;
END;

Pivot động trong MySQL

MySQL không hỗ trợ SQL động trực tiếp. Vì vậy, bạn phải tạo một thủ tục lưu trữ để thực hiện PIVOT động trong MySQL. Truy vấn dưới đây minh họa cách dùng thủ tục lưu trữ để tạo truy vấn PIVOT động.

-- Declare variables to hold the dynamic columns (cols) and the final SQL query
DELIMITER $
CREATE PROCEDURE dynamic_pivot()
BEGIN
    DECLARE cols VARCHAR(1000);
    DECLARE sql_query VARCHAR(2000);
    -- Get the list of distinct years
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('SUM(CASE WHEN year = ', year, ' THEN sales ELSE 0 END) AS ', year, '')
    ) INTO cols
    FROM city_sales;
    -- Construct the dynamic SQL query
    SET sql_query = CONCAT('SELECT city, ', cols, ' FROM city_sales GROUP BY city ORDER BY city');
    -- Prepare and execute the SQL query
    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $
DELIMITER ;

Sau khi tạo thủ tục lưu trữ, bạn cần gọi thủ tục để chạy truy vấn PIVOT động:

CALL dynamic_pivot();

Pivot động trong PostgreSQL

Tương tự, bạn có thể tạo PIVOT động trong PostgreSQL bằng truy vấn sau:

-- Block declaration to execute PL/pgSQL code in an anonymous code block
DO
$
DECLARE
    cols text;  -- Variable to store the list of columns for the dynamic query
    query text; -- Variable to store the dynamic SQL query
BEGIN
    -- Get distinct years and construct the list of SUM(CASE...) statements
    SELECT STRING_AGG(DISTINCT 'SUM(CASE WHEN year = ' || year || ' THEN sales ELSE 0 END) AS "Sales_' || year || '"', ', ')
    INTO cols
    FROM city_sales;
    -- Construct the dynamic PIVOT query
    query := 'SELECT city, ' || cols || ' FROM city_sales GROUP BY city ORDER BY city';
    -- Execute the dynamic PIVOT query
    EXECUTE query;
END
$;

Kết luận và hướng học tiếp theo

Hiểu cách dùng PIVOT trong SQL rất quan trọng nếu bạn muốn chuyển đổi và phân tích dữ liệu hiệu quả. Khi tạo bảng tổng hợp trong SQL, việc nắm các cách triển khai toán tử PIVOT trên các hệ quản trị cơ sở dữ liệu khác nhau là điều then chốt. Với vai trò nhà phân tích dữ liệu, tôi khuyến khích bạn tiếp tục luyện tập kỹ năng SQL để biết cách và thời điểm áp dụng PIVOT cho các bộ dữ liệu khác nhau.

Dù bạn là nhà phân tích dữ liệu đang tìm chỗ đứng trong ngành hay đã có kinh nghiệm, tôi khuyến nghị tham gia các khóa học Introduction to SQL và Intermediate SQL của DataCamp để cải thiện kỹ năng phân tích dữ liệu. Tôi cũng khuyến nghị khóa Data Manipulation in SQL, dạy về truy vấn lồng và các khái niệm được đề cập trong hướng dẫn này, cùng với khóa Introduction to SQL Server tập trung vào SQL Server.