SQL Tips 01 – Chuyển dữ liệu từ cột thành dòng với UNION

Khi làm việc với dữ liệu có cấu trúc (dạng bảng), ta thường thấy một số bảng dữ liệu ở dạng như sau:

Những dữ liệu dạng này thường là kết quả của:

• Thiết kế các bảng dữ liệu trong cơ sở dữ liệu quan hệ (relational database) chưa hiệu quả, hoặc
• Nhập liệu thủ công bằng tay (nhập liệu vào file excel hoặc vào các hệ thống)

Cụ thể ở ví dụ trên, thông tin PartyID ở bảng CONTRACT (ID của các bên liên quan trong hợp đồng) được lưu trữ ở tận 5 cột (do mỗi hợp đồng có tối đa 5 bên liên quan), và hợp đồng CT014241 có 2 PartyID bị trùng nhau (P950).

Bảng dữ liệu ở dạng này gây ra những khó khăn cho việc xử lý và phân tích dữ liệu:

• Khó thực hiện khi muốn GROUP BY để tính toán dữ liệu
• Nếu sau này phát sinh thêm giá trị (giả sử 1 hợp đồng được phép có tối đa 6 bên liên quan thay vì 5) thì ta sẽ phải thêm cột vào bảng và thay đổi hết tất cả các queries/views, reports có liên quan tới bảng đó

Vì vậy, khi gặp những trường hợp dữ liệu không được thiết kế và lưu trữ hiệu quả, thay vào đó bị lưu thành quá nhiều cột như trên đây, mà ta lại không có thẩm quyền để thiết kế lại cách lưu trữ dữ liệu, thì ta nên xử lý chuyển đổi chúng thành dòng. Hãy nhớ, với một hệ thống cơ sở dữ liệu dạng truyền thống (traditional database system, như SQL Server, Oracle, MySQL, …) thì: “Columns are generally more expensive than rows”. Một trong những cách xử lý đó là dùng lệnh UNION để “normalize” dữ liệu. Tham khảo code dưới đây:

SELECT
	ID AS ContractID
	, ContractNumber
	, PartyID1 as PartyID
FROM CONTRACT WHERE PartyID1 IS NOT NULL
UNION
SELECT
	ID
	, ContractNumber
	, PartyID2
FROM CONTRACT WHERE PartyID2 IS NOT NULL
UNION
SELECT
	ID
	, ContractNumber
	, PartyID3
FROM CONTRACT WHERE PartyID3 IS NOT NULL
UNION
SELECT
	ID
	, ContractNumber
	, PartyID4
FROM CONTRACT WHERE PartyID4 IS NOT NULL
UNION
SELECT
	ID
	, ContractNumber
	, PartyID5
FROM CONTRACT WHERE PartyID5 IS NOT NULL
ORDER BY ContractID, PartyID