PIVOT trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng toán tử PIVOT trong SQL Server để chuyển đổi hàng thành cột.

Thiết lập các mục tiêu

Để trình diễn, chúng tôi sẽ sử dụng các bảng production.productsproduction.categories từ cơ sở dữ liệu mẫu:

Truy vấn sau đây tìm số lượng sản phẩm cho mỗi danh mục sản phẩm:

SELECT 
    category_name, 
    COUNT(product_id) product_count
FROM 
    production.products p
    INNER JOIN production.categories c 
        ON c.category_id = p.category_id
GROUP BY 
    category_name;

Đây là đầu ra:

PIVOT trong SQL Server

Mục tiêu của chúng tôi là biến các tên danh mục từ cột đầu tiên của đầu ra thành nhiều cột và đếm số lượng sản phẩm cho mỗi tên danh mục như hình sau:

PIVOT trong SQL Server

Ngoài ra, chúng ta có thể thêm năm kiểu dáng (model year) để nhóm danh mục theo năm kiểu dáng như được hiển thị trong đầu ra sau:

PIVOT trong SQL Server

Giới thiệu về toán tử PIVOT trong SQL Server

Toán tử PIVOT trong SQL Server chuyển đổi một biểu thức có giá trị bảng. Nó biến các giá trị duy nhất trong một cột thành nhiều cột trong đầu ra và thực hiện tổng hợp trên bất kỳ giá trị cột nào còn lại.

Bạn làm theo các bước sau để tạo một truy vấn thành bảng tổng hợp (pivot table):

  • Đầu tiên, hãy chọn một tập dữ liệu cơ sở để chuyển đổi.
  • Thứ hai, tạo kết quả tạm thời bằng cách sử dụng bảng dẫn xuất hoặc biểu thức bảng chung (CTE).
  • Thứ ba, áp dụng toán tử PIVOT.

Hãy áp dụng các bước này trong ví dụ sau.

Trước tiên, hãy chọn tên danh mục và id sản phẩm từ bảng production.productsproduction.categories làm dữ liệu cơ sở để chuyển đổi:

SELECT 
    category_name, 
    product_id
FROM 
    production.products p
    INNER JOIN production.categories c 
        ON c.category_id = p.category_id

Thứ hai, tạo tập kết quả tạm thời bằng bảng dẫn xuất:

SELECT * FROM (
    SELECT 
        category_name, 
        product_id
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t

Thứ ba, áp dụng toán tử PIVOT:

SELECT * FROM   
(
    SELECT 
        category_name, 
        product_id
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN (
        [Children Bicycles], 
        [Comfort Bicycles], 
        [Cruisers Bicycles], 
        [Cyclocross Bicycles], 
        [Electric Bikes], 
        [Mountain Bikes], 
        [Road Bikes])
) AS pivot_table;

Truy vấn này tạo ra kết quả sau:

PIVOT trong SQL Server

Bây giờ, bất kỳ cột bổ sung nào mà bạn thêm vào danh sách đã chọn của truy vấn trả về dữ liệu cơ sở sẽ tự động tạo thành các nhóm hàng trong bảng tổng hợp. Ví dụ: bạn có thể thêm cột năm kiểu dáng vào truy vấn trên:

SELECT * FROM   
(
    SELECT 
        category_name, 
        product_id,
        model_year
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN (
        [Children Bicycles], 
        [Comfort Bicycles], 
        [Cruisers Bicycles], 
        [Cyclocross Bicycles], 
        [Electric Bikes], 
        [Mountain Bikes], 
        [Road Bikes])
) AS pivot_table;

Đây là đầu ra:

PIVOT trong SQL Server

Tạo giá trị cột

Trong truy vấn trên, bạn phải nhập từng tên danh mục trong dấu ngoặc đơn sau toán tử IN theo cách thủ công. Để tránh điều này, bạn có thể sử dụng hàm QUOTENAME() để tạo danh sách tên danh mục và sao chép chúng qua truy vấn.

Đầu tiên, tạo danh sách tên danh mục:

DECLARE 
    @columns NVARCHAR(MAX) = '';

SELECT 
    @columns += QUOTENAME(category_name) + ','
FROM 
    production.categories
ORDER BY 
    category_name;

SET @columns = LEFT(@columns, LEN(@columns) - 1);

PRINT @columns;

Đầu ra sẽ như thế này:

[Children Bicycles],[Comfort Bicycles],[Cruisers Bicycles],[Cyclocross Bicycles],[Electric Bikes],[Mountain Bikes],[Road Bikes]

Trong đoạn mã này:

  • Hàm QUOTENAME() sẽ bọc tên danh mục sản phẩm bởi các dấu ngoặc vuông, ví dụ:[Children Bicycles].
  • Hàm LEFT() sẽ loại bỏ dấu phẩy cuối cùng từ chuỗi @columns.

Thứ hai, sao chép danh sách tên danh mục sản phẩm từ đầu ra và dán nó vào truy vấn.

Bảng tổng hợp động

Nếu bạn thêm tên danh mục mới vào bảng production.categories, bạn cần phải viết lại truy vấn của mình, điều này không lý tưởng. Để tránh làm điều này, bạn có thể sử dụng SQL động để làm cho bảng tổng hợp động.

Trong truy vấn này, thay vì chuyển một danh sách cố định tên danh mục cho toán tử PIVOT, chúng tôi xây dựng danh sách tên danh mục và chuyển nó vào một câu lệnh SQL, sau đó thực hiện động câu lệnh này bằng stored procedure sp_executesql.

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';

-- select the category names
SELECT 
    @columns+=QUOTENAME(category_name) + ','
FROM 
    production.categories
ORDER BY 
    category_name;

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
    SELECT 
        category_name, 
        model_year,
        product_id 
    FROM 
        production.products p
        INNER JOIN production.categories c 
            ON c.category_id = p.category_id
) t 
PIVOT(
    COUNT(product_id) 
    FOR category_name IN ('+ @columns +')
) AS pivot_table;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

Trong hướng dẫn này, bạn đã học cách sử dụng toán tử PIVOT trong SQL Server để chuyển đổi hàng thành cột.

SQL Server
Bài Viết Liên Quan:
Hướng dẫn đầy đủ về Expression trong SQL Server
Trung Nguyen 18/03/2021
Hướng dẫn đầy đủ về Expression trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng biểu thức CASE, COALESCE và NULLIF trong SQL Server.

NULLIF trong SQL Server
Trung Nguyen 18/03/2021
NULLIF trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng biểu thức NULLIF trong SQL Server để trả về NULL nếu đối số đầu tiên bằng đối số thứ hai.

COALESCE trong SQL Server
Trung Nguyen 18/03/2021
COALESCE trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng biểu thức COALESCE trong SQL Server để xử lý giá trị NULL trong các truy vấn.

CASE trong SQL Server
Trung Nguyen 18/03/2021
CASE trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng biểu thức CASE trong SQL Server để thêm logic if-else vào các truy vấn SQL.