Mệnh đề PIVOT trong SQL Server

Trong SQL Server (Transact-SQL), mệnh đề PIVOT cho phép lập bảng chéo để chuyển dữ liệu từ bảng này sang bảng khác, tức là lấy kết quả tổng hợp và chuyển từ dòng này sang cột khác.

Ví dụ tính tổng rồi chuyển các hàng thành cột trong bảng dữ liệu

Cú pháp mệnh đề PIVOT

  SELECT cot_ dautien AS , 
 [giatri_chuyen1], [giatri_chuyen2], … [giatri_chuyen_n] 
 FROM 
 () AS 
 PIVOT 
 ( 
  ham_tong () 
  FOR 
  IN ([giatri_chuyen1], [giatri_chuyen2], … [giatri_chuyen_n]) 
 ) AS n>;  ) AS n>; 

Tên biến hoặc giá trị biến

cot_dautien

Cột hoặc biểu thức sẽ trở thành cột đầu tiên trong bảng mới sau khi chuyển đổi.

bidanh_cot_dautien

Tên của cột đầu tiên trong bảng mới sau khi chuyển.

giatri_chuyen1, giatri_chuyen2 ,. giatri_chuyen_n

Danh sách các giá trị cần chuyển.

bang_nguon

Câu lệnh SELECT đưa dữ liệu nguồn (dữ liệu ban đầu) vào bảng mới.

bidanh_bang_nguon

Bí danh của bang_nguon

ham_tong

Các hàm tính tổng như SUM, COUNT, MIN, MAX hoặc AVG.

cot_tong

Cột hoặc biểu thức được sử dụng với ham_tong.

cot_chuyen

Cột chứa giá trị được chuyển.

bidanh_bang_chuyen

Bí danh của bảng sau khi chuyển.

Mệnh đề PIVOT có thể được sử dụng trong các phiên bản sau của SQL Server:SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008 và SQL Server 2005.

Để làm theo các bước trong hướng dẫn, hãy xem phần DDL để tạo bảng và DML để tạo dữ liệu ở cuối bài viết này, sau đó thử chạy trên cơ sở dữ liệu của riêng bạn.

Ví dụ với mệnh đề PIVOT

Chúng ta có bảng với dữ liệu như hình dưới đây.

Chạy lệnh SQL dưới đây để tạo truy vấn chéo với điều khoản PIVOT.

  SELECT 'TongLuo ng' AS TongLuongTheoPhong, 
 [30], [45] 
 FROM 
 (SELECT id_phong, luong 
 FROM nhanvien) AS BangNguon 
 PIVOT 
 ( 
  SUM(luong) 
  FOR id_phong IN ([30], [45]) 
 ) AS BangChuyen; 

Kết quả trả về sẽ giống như sau.

Ví dụ trên tạo một bảng sau khi dữ liệu được chuyển hết, cho biết tổng lương của phòng có ID là 30 và của phòng có ID là 45. Kết quả là trên một hàng có 2 cột, mỗi cột là 1 phòng.

Chỉ định cột trong bảng mới của truy vấn chéo

Đầu tiên cần xác định trường thông tin muốn đưa vào bảng chuyển. Trong ví dụ này, TongLuong là cột đầu tiên, sau đó là 2 cột id_phong 30 và id_phong 45.

  SEL ECT 'TongLuong' AS TongLuongTheoPhong, 
  [30], [ 45] 

Xác định dữ liệu trong bảng nguồn

Tiếp theo là câu lệnh SELECT sẽ trả về dữ liệu nguồn cho bảng mới.

Trong ví dụ này, nó là id_phong và từ bảng.

  (SELECT id_phong, lu ong 
  FROM nhanvien) AS Ba ngNguon 

Cần chỉ định bí danh cho truy vấn nguồn, trong ví dụ này là BangNguon.

Xác định hàm tính tổng

Các hàm có thể được sử dụng trong các truy vấn chéo bao gồm SUM, COUNT, MIN, MAX và AVG. Trong ví dụ này, hàm tổng SUM.

 PIVOT 
 (SUM(luong) 

Xác định giá trị sẽ được chuyển

Cuối cùng, giá trị cần được chuyển để bao gồm kết quả. Đây sẽ là tiêu đề cột trong truy vấn chéo.

Trong ví dụ này, chúng ta chỉ cần trả về id_folder 30 và 45. Các giá trị này sẽ là tên cột trong bảng mới. Hãy nhớ rằng các giá trị này là danh sách các giá trị giới hạn của id_phong và không nhất thiết phải chứa tất cả các giá trị.

 FOR id_phong IN ([30], [45]) 

DDL / DML để làm ví dụ

Nếu bạn có cơ sở dữ liệu và muốn thử các ví dụ trong sách hướng dẫn PIVOT ở trên, bạn sẽ cần DDL / DML.

DDL - Ngôn ngữ Định nghĩa Dữ liệu là các lệnh tạo bảng (CREATE TABLE) để sử dụng trong ví dụ mệnh đề PIVOT.

  CREATE TA BLE phong 
 ( id_phong INT NOT NULL, 
 ten_phong VARCHAR(50) NOT NULL, 
 CONSTRAINT pk_phong PRIMARY KEY (id_phong) 
 ) ; 

  CREATE T ABLE nhanvien 
 ( so_nhanvien INT NOT NULL, 
 ho VARCHAR(50) NOT NULL, 
 ten VARCHAR(50) NOT NULL, 
 luong INT, 
 id_phong INT, 
 CONSTRAINT pk_nhanvien PRIMARY KEY (so_nhanvien) 
 ) ; 

DML - Ngôn ngữ thao tác dữ liệu là các câu lệnh INSERT để tạo dữ liệu cần thiết cho bảng.

  INSERT INTO phong 
 (id_phong, ten_phong) 
 VALUES 
  (30, 'Ketoan '); 

  INSERT INTO  phong 
 (id_phong, ten_phong) 
 VALUES 
 (45, 'Banhang'); 

  INSERT INTO nhanvien 
 (so_nhanvien, ho, ten, luong, id_phong) 
 VALUES 
  (12009, 'Nguye n', 'Huong', 54000, 45); 

  INSERT INTO  nhanvien 
 (so_nhanvien, ho, ten, luong, id_phong) 
 VALUES 
  (34974, 'Pham', 'Flowers', 80000, 45); 

  INSERT INTO anvien 
 (so_nhanvien, ho, ten, luong, id_phong) 
 VALUES 
  (34987, 'Phan', 'La n', 42000, 45); 

  INSERT  INTO nhanvien 
 (so_nhanvien, ho, ten, luong, id_phong) 
 VALUES 
  45001, 'Tr an', 'Hue', 57500, 30); 

  INSERT INTO home 
 (so_nhanvien, ho, ten, luong, id_phong) 
 VALUES 
  (75623, 'Vu', 'Hong' , 65000, 30);