Chuyển dữ liệu từ hàng thành cột với SQL Pivot và Unpivot

Ngày đăng: 26/03/2024

Pivot và Unpivot là hai thao tác biến đổi dữ liệu phổ biến trong quá trình xử lý và làm sạch dữ liệu. Việc thành thạo các thao tác này trong các công cụ khác nhau (SQL, Power BI, Python, Excel…) sẽ giúp các bạn chủ động trong quá trình xử thực hiện phân tích. Trong bài viết này, các bạn sẽ hiểu rõ khái niệm Pivot, Unpivot và cách thực hiện nó trong SQL Server.

1. Pivot và Unpivot là gì?

Pivot và Unpivot là các phương pháp phổ biến trong SQL, dùng để chuyển dữ liệu từ hàng thành cột.

Pivot là gì

Pivot là một phép biến đổi dữ liệu trong SQL Server, cho phép chuyển đổi các giá trị từ hàng thành cột. Kỹ thuật này thường được sử dụng để chuyển dữ liệu từ dạng hàng dọc thành cột ngang.

Ví dụ doanh nghiệp bán lẻ A theo dõi đơn hàng hàng trên bảng dữ liệu như sau:

Region

ProductCategory

Month

SalesAmount

North

Electronics

Jan-22

10000

North

Electronics

Feb-22

12000

North

Clothing

Jan-22

8000

North

Clothing

Feb-22

9000

South

Electronics

Jan-22

9000

South

Electronics

Feb-22

11000

South

Clothing

Jan-22

7000

South

Clothing

Feb-22

8000

Khi công ty A muốn so sánh hiệu suất bán hàng giữa các khu vực và danh mục sản phẩm trong các tháng, Pivot được áp dụng để chuyển đổi dữ liệu sao cho mỗi danh mục sản phẩm trở thành một cột riêng biệt, giúp dễ dàng theo dõi và phân tích.

Region

ProductCategory

Jan-22 SalesAmount

Feb-22 SalesAmount

North

Electronics

10000

12000

North

Clothing

8000

9000

South

Electronics

9000

11000

South

Clothing

7000

8000

Sau khi sử dụng Pivot

Unpivot là gì

Unpivot là phép biến đổi dữ liệu ngược lại với Pivot, cho phép chuyển đổi các giá trị từ các cột thành hàng. Unpivot cho phép bạn “phân giải” dữ liệu từ dạng hàng ngang thành cột dọc, tạo điều kiện thuận lợi cho việc phân tích và báo cáo.

Trong ví dụ của công ty B, dữ liệu về doanh số bán hàng của các sản phẩm được lưu trữ trong các cột riêng biệt, gây khó khăn trong việc kiểm soát số lượng cột của bảng và phân tích dữ liệu:

Month

Product A Quantity

Product A Revenue

Product B Quantity

Product B Revenue

January

100

5000

150

7500

February

120

6000

180

9000

Bằng cách sử dụng Unpivot, chúng ta có thể chuyển đổi dữ liệu từ dạng hàng ngang thành cột dọc, giúp thuận tiện hơn cho việc phân tích và báo cáo.

Month

Product

Quantity

Revenue

January

Product_A

100

5000

January

Product_B

150

7500

February

Product_A

120

6000

February

Product_B

180

9000

Sau khi sử dụng Unpivot

2. Tác dụng của Pivot và Unpivot

Tác dụng của Pivot

  • Pivot giúp dễ dàng tổng hợp và phân loại dữ liệu.
  • Cho phép biến đổi dữ liệu từ dạng dài thành rộng để phù hợp với yêu cầu báo cáo hoặc phân tích.

Tác dụng của Unpivot

  • Unpivot giúp chuẩn hóa dữ liệu bằng cách chuyển từ dạng cột thành hàng.
  • Được sử dụng để giảm bớt việc lặp lại dữ liệu và tạo ra dữ liệu một cách rõ ràng và dễ hiểu hơn.

3. Cách sử dụng Pivot và Unpivot trong SQL

Sử dụng cú pháp Pivot

Định nghĩa các cột mà bạn muốn chuyển đổi thành dữ liệu ngang và áp dụng hàm tổng hợp hoặc hàm tính toán.

Dưới đây là cú pháp của Pivot:

SELECT <non-pivoted column>,  
[first pivoted column] AS <column name>,  
[second pivoted column] AS <column name>,  
...  
[last pivoted column] AS <column name>  

FROM  
(<SELECT query that produces the data>)  
AS <alias for the source query>  

PIVOT  
(  
<aggregation function>(<column being aggregated>)  

FOR  
[<column that contains the values that will become column headers>]  
IN ( [first pivoted column], [second pivoted column],  
... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>; 

Để minh hoạ cụ thể, ta thực hiện trên ví dụ sau:

Ta có bảng Sales dưới đây:

Thông tin bảng Sales

Nếu tính số lượng đơn hàng (số lượng SaleOrderNumber) theo khu vực (SalesTerritoryCountry) theo cách thông thường, ta thực hiện như sau:

SELECT count(SalesOrderNumber) as NBR_ORDER,SalesTerritoryCountry 
FROM Sales 
GROUP BY SalesTerritoryCountry 

Kết quả theo cách thực hiện thông thường

Tuy nhiên, để trải thông tin SalesTerritoryCountry thành các cột, ta thực câu query sau:

SELECT * 

FROM  
( 
SELECT  
SalesOrderNumber,  
[SalesTerritoryCountry]  
FROM Sales 
) AS TableToPivot  

PIVOT  
(  
COUNT( SalesOrderNumber) 
FOR [SalesTerritoryCountry] IN ([Australia], 
[Canada], 
[France], 
[Germany], 
[United Kingdom], 
[United States]  
)) AS PivotTable 

Kết quả bảng PIVOT_SALES của câu query trả về:

Kết quả bảng Sale sau khi sử dụng Pivot để chuyển hàng thành cột
Kết quả bảng Sale sau khi sử dụng Pivot để chuyển hàng thành cột

Sử dụng cú pháp Unpivot

Xác định các cột mà bạn muốn chuyển đổi thành dữ liệu dọc và chỉ định cột giá trị, cột đối tượng.

Dựa vào bảng Pivot (PIVOT_SALES) vừa xong, ta thực hiện Unpivot ngược lại:

Thực hiện câu query sau:

SELECT territory, NBR_ORDER 

FROM 
( 
SELECT  
[Australia], 
[Canada], 
[France], 
[Germany], 
[United Kingdom], 
[United States] 
FROM PIVOT_SALES 
) p 

UNPIVOT 
( 
NBR_ORDER for territory IN 
([Australia], 
[Canada], 
[France], 
[Germany], 
[United Kingdom], 
[United States]) 
) AS upvt 
Kết quả bảng PIVOT_SALES sau khi Unpivot

Cách chuyển đổi hàng cột với CASE WHEN

Bên cạnh đó, để trải giá trị từ hàng thành các cột, ngoài Pivot, ta có thể sử dụng CASE WHEN thủ công như sau:

Thực hiện câu query:

SELECT  
'NBR_ORDER' as NBR_ORDER 

,COUNT(CASE WHEN SalesTerritoryCountry = 'Australia' THEN 			SalesOrderNumber ELSE NULL END ) AS 'Australia' 

,COUNT(CASE WHEN SalesTerritoryCountry = 'Canada' THEN SalesOrderNumber ELSE NULL END ) AS 'Canada' 

,COUNT(CASE WHEN SalesTerritoryCountry = 'France' THEN SalesOrderNumber ELSE NULL END ) AS 'France' 

,COUNT(CASE WHEN SalesTerritoryCountry = 'Germany' THEN SalesOrderNumber ELSE NULL END ) AS 'Germany' 

,COUNT(CASE WHEN SalesTerritoryCountry = 'United Kingdom' THEN SalesOrderNumber ELSE NULL END ) AS 'United Kingdom' 

,COUNT(CASE WHEN SalesTerritoryCountry = 'United States' THEN SalesOrderNumber ELSE NULL END ) AS 'United States' 

FROM Sales 
Kết quả đã trải giá trị Country từ hàng thành cột

Kết luận

Pivot và Unpivot là hai phép biến đổi quan trọng trong SQL Server, cho phép bạn chuyển đổi dữ liệu giữa các dạng cột và hàng một cách linh hoạt và hiệu quả. Hiểu và sử dụng chúng đúng cách sẽ giúp tăng hiệu suất và linh hoạt trong quản lý cơ sở dữ liệu của bạn.

Để thực hành và cập nhật thêm nhiều kiến thức về SQL, các bạn có thể tham khảo thêm các bài viết tại SQL Archives – Datapot.vn

Tải SQL tại đây: SQL Server Downloads | Microsoft

Ngoài ra, chúng mình cũng có những hướng dẫn chi tiết khi xử lý thao tác Pivot và Unpivot trong Power Query (làm việc với Power BI) tại đây: Power BI: Pivot và Unpivot trong Power Query (datapot.vn)

Chia sẻ bài viết này

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *