Hàm pivot trong SQL Server: Hướng dẫn từ A – Z

Nếu như đang nghiên cứu về quản lý cơ sở dữ liệu và lập trình ứng dụng, chắc hẳn bạn đã từng nghe đến hàm pivot trong SQL server. Đây là một kiến thức cơ bản quan trongj mà bất kỳ ai cũng cần phải nắm trước khi muốn phát triển ở trong ngành lập trình. Vậy thì Pivot SQL server là gì? Cách dùng, ví dụ ra sao? Cùng theo dõi bài viết của Comdy.vn để rõ hơn chi tiết nhé.

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 trong sql):

• Đầ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 sql server.

Ví dụ về SQL Server pivot

Để hiểu rõ hơn về pivot in sql server, 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.products và production.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ử sql 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;

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;

Tạo giá trị cột trong SQL server

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 trong SQL server

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ử select pivot sql server, 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ử query pivot sql server để chuyển đổi hàng thành cột.