SQL Pivot: Chuyển Hàng Thành Cột — Hướng Dẫn Toàn Diện

Chuyển Hàng Thành Cột Trong SQL Nghĩa Là Gì?

Pivot trong SQL đề cập đến việc chuyển dữ liệu từ định dạng theo hàng sang định dạng theo cột. Chuyển đổi này hữu ích cho báo cáo và phân tích dữ liệu, cho phép có một chế độ xem dữ liệu có cấu trúc và gọn hơn. Việc chuyển hàng thành cột cũng giúp người dùng phân tích và tóm tắt dữ liệu theo cách làm nổi bật các insight chính rõ ràng hơn.

Hãy xem ví dụ sau: tôi có một bảng giao dịch bán hàng hằng ngày, mỗi hàng ghi lại ngày, tên sản phẩm và doanh số.

Bằng cách pivot bảng này, tôi có thể tái cấu trúc để mỗi sản phẩm là một cột, với dữ liệu doanh số cho từng ngày nằm dưới cột tương ứng. Lưu ý rằng việc tổng hợp cũng diễn ra.

Trước đây, thao tác pivot đòi hỏi các truy vấn SQL phức tạp với tổng hợp có điều kiện. Theo thời gian, các hệ SQL đã phát triển, với nhiều cơ sở dữ liệu hiện đại bổ sung toán tử PIVOT và UNPIVOT để cho phép chuyển đổi hiệu quả và trực quan hơn.

Tìm Hiểu Về SQL Pivot Chuyển Hàng Thành Cột

Thao tác pivot trong SQL biến đổi dữ liệu bằng cách chuyển các giá trị ở hàng thành các cột. Sau đây là cú pháp và cấu trúc cơ bản của SQL pivot với các phần sau:

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

• Subquery: Tiểu truy vấn chứa nguồn dữ liệu hoặc bảng sẽ đưa vào bảng pivot 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 pivot.

-- Select static columns and pivoted columns
SELECT <static columns>, [pivoted columns]
FROM
    (
        -- Subquery defining source data for pivot
        <subquery that defines data>
    ) AS source
PIVOT
(
    -- Aggregate function applied to value column, creating new columns
    <aggregation function>(<value column>)
    FOR <column to pivot> IN ([list of pivoted columns])
) AS pivot_table;

Hãy xem ví dụ từng bước dưới đây để minh họa cách chuyển hàng thành cột trong SQL. Xem bảng SalesData bên dưới.

Ví dụ bảng cần chuyển đổi bằng toán tử SQL PIVOT. Ảnh: Tác giả.

Tôi muốn pivot dữ liệu này để so sánh doanh số hằng ngày của từng sản phẩm. Tôi sẽ bắt đầu bằng cách chọn tiểu truy vấn làm nền cho toán tử PIVOT.

-- Subquery defining source data for pivot
SELECT Date, Product, Sales
FROM SalesData;

Giờ tôi sẽ dùng toán tử PIVOT để chuyển các giá trị Product thành cột và tổng hợp Sales bằng toán tử SUM.

-- Select Date and pivoted columns for each product
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subquery to fetch Date, Product, and Sales columns
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggregate Sales by Product, pivoting product values to columns
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Ví dụ kết quả chuyển đổi khi pivot hàng thành cột trong SQL. Ảnh: Tác giả.

Dù pivot giúp đơn giản hóa phần tóm tắt dữ liệu, kỹ thuật này có thể nảy sinh vấn đề. Dưới đây là những thách thức tiềm ẩn với SQL pivot và cách xử lý.

• Tên cột động: Khi các giá trị cần pivot (ví dụ loại Sản phẩm) chưa biết, việc cố định tên cột sẽ không hiệu quả. Một số cơ sở dữ liệu như SQL Server hỗ trợ SQL động với stored procedure để tránh vấn đề này, trong khi số khác yêu cầu xử lý ở tầng ứng dụng.

• Xử lý giá trị NULL: Khi không có dữ liệu cho một cột đã pivot, kết quả có thể bao gồm NULL. Bạn có thể dùng COALESCE để thay NULL bằng 0 hoặc giá trị thay thế khác.

• Tương thích giữa các CSDL: Không phải mọi CSDL đều hỗ trợ trực tiếp toán tử PIVOT. Bạn có thể đạt kết quả tương tự với câu lệnh CASE và tổng hợp có điều kiện nếu biến thể SQL của bạn không hỗ trợ.

SQL Pivot Chuyển Hàng Thành Cột: Ví Dụ Và Trường Hợp Sử Dụng

Có nhiều phương pháp pivot dữ liệu trong SQL, tùy thuộc CSDL sử dụng hoặc các yêu cầu khác. Trong khi toán tử PIVOT thường dùng ở SQL Server, các kỹ thuật khác như câu lệnh CASE cho phép chuyển đổi tương tự mà không cần hỗ trợ PIVOT trực tiếp. Tôi sẽ trình bày hai phương pháp phổ biến để pivot dữ liệu trong SQL, cùng ưu và nhược điểm.

Sử dụng toán tử PIVOT

Toán tử PIVOT, có trong SQL Server, cung cấp cách trực tiếp để chuyển hàng thành cột bằng cách chỉ định hàm tổng hợp và xác định các cột cần pivot.

Xem bảng sau có tên sales_data.

Ví dụ bảng Orders cần chuyển đổi bằng toán tử PIVOT. Ảnh: Tác giả.

Tôi sẽ dùng toán tử PIVOT để tổng hợp dữ liệu sao cho tổng sales_revenue mỗi năm hiển thị theo cột.

-- Use PIVOT to aggregate sales revenue by year
SELECT *
FROM (
    -- Select the relevant columns from the source table
    SELECT sale_year, sales_revenue
    FROM sales_data
) AS src
PIVOT (
    -- Aggregate sales revenue for each year
    SUM(sales_revenue)
    -- Create columns for each year
    FOR sale_year IN ([2020], [2021], [2022], [2023])
) AS piv;

Ví dụ kết quả chuyển đổi sử dụng SQL PIVOT. Ảnh: Tác giả.

Sử dụng toán tử PIVOT có các ưu và nhược điểm sau:

• Ưu điểm: Phương pháp hiệu quả khi các cột được đánh chỉ mục phù hợp. Cú pháp đơn giản, dễ đọc.

• Hạn chế: Không phải mọi CSDL đều hỗ trợ toán tử PIVOT. Cần chỉ định trước các cột, và pivot động sẽ phức tạp hơn.

Pivot thủ công với câu lệnh CASE

Bạn cũng có thể dùng câu lệnh CASE để pivot thủ công dữ liệu trong các CSDL không hỗ trợ toán tử PIVOT, như MySQL và PostgreSQL. Cách này dùng tổng hợp có điều kiện bằng cách đánh giá từng hàng và gán điều kiện các giá trị vào cột mới dựa trên tiêu chí cụ thể.

Ví dụ, ta có thể pivot thủ công dữ liệu trong cùng bảng sales_data bằng câu lệnh CASE.

-- Aggregate sales revenue by year using CASE statements
SELECT 
    -- Calculate total sales revenue for each year
    SUM(CASE WHEN sale_year = 2020 THEN sales_revenue ELSE 0 END) AS sales_2020,
    SUM(CASE WHEN sale_year = 2021 THEN sales_revenue ELSE 0 END) AS sales_2021,
    SUM(CASE WHEN sale_year = 2022 THEN sales_revenue ELSE 0 END) AS sales_2022,
    SUM(CASE WHEN sale_year = 2023 THEN sales_revenue ELSE 0 END) AS sales_2023
FROM 
    sales_data;

Ví dụ kết quả chuyển đổi sử dụng câu lệnh SQL CASE. Ảnh: Tác giả.

Dùng câu lệnh CASE để chuyển đổi có các ưu và nhược điểm sau:

• Ưu điểm: Hoạt động trên mọi CSDL SQL và linh hoạt để tạo cột mới động, ngay cả khi tên sản phẩm chưa biết hoặc thay đổi thường xuyên.

• Hạn chế: Truy vấn có thể trở nên phức tạp và dài nếu có nhiều cột cần pivot. Do có nhiều điều kiện kiểm tra, phương pháp này thường chậm hơn đôi chút so với toán tử PIVOT.

Lưu Ý Hiệu Năng Khi Chuyển Hàng Thành Cột

Pivot trong SQL có thể tác động đến hiệu năng, đặc biệt với tập dữ liệu lớn. Dưới đây là một số mẹo và thực hành tốt giúp bạn viết truy vấn pivot hiệu quả, tối ưu hiệu năng và tránh cạm bẫy thường gặp.

Thực hành tốt

Dưới đây là những thực hành tốt để tối ưu truy vấn và cải thiện hiệu năng.

• Chiến lược lập chỉ mục: Lập chỉ mục phù hợp là chìa khóa tối ưu truy vấn pivot, giúp SQL truy xuất và xử lý dữ liệu nhanh hơn. Luôn đánh chỉ mục cho các cột thường dùng trong mệnh đề WHERE hoặc các cột bạn nhóm để giảm thời gian quét.

• Tránh pivot lồng nhau: Xếp chồng nhiều thao tác pivot trong một truy vấn khó đọc và chậm thực thi. Hãy đơn giản hóa bằng cách tách truy vấn thành các phần hoặc dùng bảng tạm.

• Giới hạn cột và hàng khi pivot: Chỉ pivot các cột cần thiết cho phân tích vì pivot nhiều cột tốn tài nguyên và có thể tạo bảng rất lớn.

Tránh cạm bẫy thường gặp

Dưới đây là những lỗi phổ biến bạn có thể gặp trong truy vấn pivot và cách tránh.

• Quét toàn bộ bảng không cần thiết: Truy vấn pivot có thể kích hoạt quét toàn bảng, nhất là khi không có chỉ mục phù hợp. Tránh điều này bằng cách lập chỉ mục các cột chính và lọc dữ liệu trước khi áp dụng pivot.

• Dùng SQL động quá thường xuyên để pivot: SQL động có thể làm chậm hiệu năng do biên dịch lại truy vấn. Để tránh, hãy lưu đệm hoặc giới hạn pivot động cho các tình huống cụ thể và cân nhắc xử lý cột động ở tầng ứng dụng khi có thể.

• Tổng hợp trên tập dữ liệu lớn mà không lọc trước: Các hàm tổng hợp như SUM hoặc COUNT trên dữ liệu lớn có thể làm chậm CSDL. Thay vì pivot toàn bộ, hãy lọc trước bằng mệnh đề WHERE.

• Giá trị NULL trong cột đã pivot: Pivot thường tạo ra NULL khi không có dữ liệu cho một cột cụ thể. Điều này có thể làm chậm truy vấn và khó diễn giải. Hãy dùng các hàm như COALESCE để thay NULL bằng giá trị mặc định.

• Chỉ kiểm thử với dữ liệu mẫu: Truy vấn pivot có thể hành xử khác khi chạy trên dữ liệu lớn do nhu cầu bộ nhớ và xử lý tăng. Luôn kiểm thử trên dữ liệu thực hoặc mẫu đại diện để đánh giá chính xác tác động hiệu năng.

Hãy thử lộ trình nghề nghiệp SQL Server Developer của chúng tôi, bao quát từ giao dịch, xử lý lỗi đến cải thiện hiệu năng truy vấn.

Triển Khai Theo Từng Hệ Quản Trị CSDL

Thao tác pivot khác nhau đáng kể giữa các CSDL như SQL Server, MySQL và Oracle. Mỗi hệ có cú pháp và hạn chế riêng. Tôi sẽ trình bày ví dụ pivot dữ liệu trên các CSDL khác nhau và các đặc điểm chính.

SQL Server

SQL Server cung cấp toán tử PIVOT tích hợp, rất trực quan khi chuyển hàng thành cột. Toán tử PIVOT dễ sử dụng và tích hợp với các hàm tổng hợp mạnh mẽ của SQL Server. Các đặc điểm chính của pivot trong SQL gồm:

• Hỗ trợ trực tiếp PIVOT và UNPIVOT: Toán tử PIVOT của SQL Server cho phép chuyển đổi hàng thành cột nhanh chóng. Toán tử UNPIVOT cũng có thể đảo ngược quá trình này.

• Tùy chọn tổng hợp: Toán tử PIVOT hỗ trợ nhiều hàm tổng hợp như SUM, COUNT và AVG.

Hạn chế của toán tử PIVOT trong SQL Server là yêu cầu biết trước các giá trị cột sẽ pivot, làm nó kém linh hoạt với dữ liệu thay đổi động.

Trong ví dụ dưới đây, toán tử PIVOT chuyển các giá trị Product thành cột và tổng hợp Sales bằng SUM.

-- Select Date and pivoted columns for each product
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subquery to fetch Date, Product, and Sales columns
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggregate Sales by Product, pivoting product values to columns
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Tôi khuyến nghị khóa Introduction to SQL Server của DataCamp để nắm vững những điều cơ bản của SQL Server cho phân tích dữ liệu.

MySQL

MySQL không hỗ trợ gốc toán tử PIVOT. Tuy nhiên, bạn có thể dùng câu lệnh CASE để pivot thủ công hàng thành cột và kết hợp các hàm tổng hợp khác như SUM, AVG và COUNT. Dù linh hoạt, cách này có thể trở nên phức tạp nếu có nhiều cột cần pivot.

Truy vấn dưới đây đạt cùng kết quả như ví dụ PIVOT trong SQL Server bằng cách tổng hợp có điều kiện doanh số cho từng sản phẩm với câu lệnh CASE.

-- Select Date and pivoted columns for each product
SELECT 
    Date,
    -- Use CASE to create a column for Laptop and Mouse sales
    SUM(CASE WHEN Product = 'Laptop' THEN Sales ELSE 0 END) AS Laptop,
    SUM(CASE WHEN Product = 'Mouse' THEN Sales ELSE 0 END) AS Mouse
FROM SalesData
GROUP BY Date;

Oracle

Oracle hỗ trợ toán tử PIVOT, cho phép chuyển hàng thành cột một cách trực quan. Giống SQL Server, bạn cần chỉ rõ các cột sẽ chuyển đổi.

Trong truy vấn dưới đây, toán tử PIVOT chuyển các giá trị ProductName thành cột và tổng hợp SalesAmount bằng SUM.

SELECT *
FROM (
    -- Source data selection
    SELECT SaleDate, ProductName, SaleAmount FROM SalesData
)
PIVOT (
    -- Aggregate Sales by Product, creating pivoted columns
    SUM(SaleAmount)
    FOR ProductName IN ('Laptop' AS Laptop, 'Mouse' AS Mouse)
);

Ví dụ kết quả chuyển đổi dùng toán tử SQL PIVOT trong Oracle. Ảnh: Tác giả.

Kỹ Thuật Nâng Cao Khi Pivot Hàng Thành Cột Trong SQL

Các kỹ thuật nâng cao để pivot hàng thành cột hữu ích khi bạn cần linh hoạt xử lý dữ liệu phức tạp. Kỹ thuật động và xử lý đồng thời nhiều cột cho phép bạn chuyển đổi dữ liệu trong các kịch bản mà pivot tĩnh bị hạn chế. Hãy cùng khám phá chi tiết hai phương pháp này.

Pivot động

Pivot động cho phép bạn tạo truy vấn pivot tự động thích ứng với thay đổi của dữ liệu. Kỹ thuật này đặc biệt hữu ích khi có các cột thay đổi thường xuyên, như tên sản phẩm hoặc danh mục, và bạn muốn truy vấn tự động bao gồm mục mới mà không cần cập nhật thủ công.

Giả sử ta có bảng SalesData và có thể tạo pivot động điều chỉnh khi có sản phẩm mới được thêm vào. Trong truy vấn dưới đây, @columns xây dựng động danh sách các cột đã pivot, và sp_executesql chạy đoạn SQL được tạo.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- Step 1: Generate a list of distinct products to pivot
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ') 
FROM (SELECT DISTINCT Product FROM SalesData) AS products;
-- Step 2: Build the dynamic SQL query
SET @sql = N'
SELECT Date, ' + @columns + '
FROM 
    (SELECT Date, Product, Sales FROM SalesData) AS source
PIVOT
(
    SUM(Sales)
    FOR Product IN (' + @columns + ')
) AS pivot_table;';
-- Step 3: Execute the dynamic SQL
EXEC sp_executesql @sql;

Xử lý nhiều cột

Trong các tình huống cần pivot đồng thời nhiều cột, bạn sẽ dùng toán tử PIVOT và thêm các kỹ thuật tổng hợp để tạo nhiều cột trong cùng truy vấn. 

Trong ví dụ dưới đây, tôi đã pivot các cột Sales và Quantity theo Product.

-- Pivot Sales and Quantity for Laptop and Mouse by Date
SELECT 
    p1.Date, 
    p1.[Laptop] AS Laptop_Sales, 
    p2.[Laptop] AS Laptop_Quantity, 
    p1.[Mouse] AS Mouse_Sales, 
    p2.[Mouse] AS Mouse_Quantity
FROM 
    (
        -- Pivot for Sales
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Sales FROM SalesData) AS source
        PIVOT
            (SUM(Sales) FOR Product IN ([Laptop], [Mouse])) AS pivot_sales
    ) p1
JOIN
    (
        -- Pivot for Quantity
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Quantity FROM SalesData) AS source
        PIVOT
            (SUM(Quantity) FOR Product IN ([Laptop], [Mouse])) AS pivot_quantity
    ) p2
ON p1.Date = p2.Date;

Ví dụ kết quả chuyển đổi nhiều cột bằng toán tử SQL PIVOT. Ảnh: Tác giả.

Pivot nhiều cột cho phép báo cáo chi tiết hơn bằng cách pivot nhiều thuộc tính cho mỗi mục, mang lại insight phong phú. Tuy nhiên, cú pháp có thể phức tạp, nhất là khi có nhiều cột. Có thể cần cố định thủ công trừ khi kết hợp với kỹ thuật pivot động, làm tăng thêm độ phức tạp.

Kết Luận

Chuyển hàng thành cột là một kỹ thuật SQL đáng để học. Tôi đã thấy kỹ thuật pivot trong SQL được dùng để tạo bảng giữ chân theo cohort, nơi bạn theo dõi mức độ giữ chân người dùng theo thời gian. Tôi cũng thấy pivot SQL được dùng khi phân tích dữ liệu khảo sát, nơi mỗi hàng đại diện một người trả lời và mỗi câu hỏi có thể được pivot thành cột riêng. 

Khóa Reporting in SQL của chúng tôi là lựa chọn tuyệt vời nếu bạn muốn học thêm về tóm tắt và chuẩn bị dữ liệu cho trình bày và/hoặc xây dựng dashboard. Lộ trình nghề nghiệp Associate Data Analyst in SQL và Associate Data Engineer in SQL cũng rất đáng cân nhắc, và giúp CV của bạn nổi bật, hãy đăng ký ngay hôm nay.