ส่วนคำสั่ง PIVOT ใน SQL Server

ใน SQL Server (Transact-SQL) ส่วนคำสั่ง PIVOT อนุญาตให้ใช้ตารางข้ามเพื่อถ่ายโอนข้อมูลจากตารางหนึ่งไปยังอีกตารางหนึ่ง กล่าวคือ รับผลลัพธ์รวมและย้ายจากบรรทัดหนึ่งไปอีกคอลัมน์หนึ่ง

ตัวอย่างจะคำนวณผลรวมแล้วส่งแถวเป็นคอลัมน์ในตารางข้อมูล

รูปแบบประโยคคำสั่ง 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>; 

ชื่อตัวแปรหรือค่าตัวแปร

cot_dautien

คอลัมน์หรือนิพจน์จะกลายเป็นคอลัมน์แรกในตารางใหม่หลังการเปลี่ยนแปลง

bidanh_cot_dautien

ชื่อของคอลัมน์แรกในตารางใหม่หลังการโอน

giatri_chuyen1, giatri_chuyen2, . giatri_chuyen_n

รายการค่าที่จะโอน

bang_nguon

คำสั่ง SELECT จะนำข้อมูลต้นทาง (ข้อมูลเริ่มต้น) ไปไว้ในตารางใหม่

bidanh_bang_nguon

นามแฝงของ bang_nguon

ham_tong

ฟังก์ชันผลรวม เช่น SUM, COUNT, MIN, MAX หรือ AVG

cot_tong

คอลัมน์หรือนิพจน์ใช้กับ ham_tong

cot_chuyen

คอลัมน์มีค่าที่จะโอน

bidanh_bang_chuyen

นามแฝงของตารางหลังจากโอน

ส่วนคำสั่ง PIVOT สามารถใช้ได้ในเวอร์ชันที่ใหม่กว่าของ SQL Server:SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008 และ SQL Server 2005

ในการทำตามขั้นตอนในบทช่วยสอน ให้ดูส่วน DDL สำหรับการสร้างตารางและ DML เพื่อสร้างข้อมูลท้ายบทความนี้ จากนั้นลองใช้ฐานข้อมูลของคุณเอง

ตัวอย่างที่มีส่วนคำสั่ง PIVOT

เรามีตารางที่มีข้อมูลดังแสดงด้านล่าง

รันคำสั่ง SQL ด้านล่างเพื่อสร้าง cross-query ด้วย ข้อ 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; 

ผลลัพธ์ที่ได้จะมีลักษณะดังนี้

ตัวอย่างด้านบนสร้างตารางหลังจากโอนข้อมูลแล้ว โดยระบุว่าเงินเดือนทั้งหมดของห้องมี ID 30 และห้องมี ID 45 ผลลัพธ์จะอยู่ในแถวที่มี 2 คอลัมน์ แต่ละคอลัมน์คือ 1 ห้อง

ระบุคอลัมน์ในตารางใหม่ของ cross-query

ขั้นแรกคุณต้องกำหนดว่าข้อมูลใดที่จะรวมไว้ในตารางการถ่ายโอน ในตัวอย่างนี้ TongLuong คือคอลัมน์แรก ตามด้วย 2 คอลัมน์ id_phong 30 และ id_phong 45

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

กำหนดข้อมูลในตารางต้นฉบับ

ถัดไปคือคำสั่ง SELECT ที่จะส่งคืนข้อมูลต้นฉบับสำหรับตารางใหม่

ในตัวอย่างนี้ มันคือ id_phong และจากตาราง

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

จำเป็นต้องระบุนามแฝงสำหรับคิวรีต้นทาง ในตัวอย่างนี้คือ BangNguon

กำหนดฟังก์ชันการคำนวณทั้งหมด

ฟังก์ชันที่สามารถใช้ในการสืบค้นข้ามได้ ได้แก่ SUM, COUNT, MIN, MAX และ AVG ในตัวอย่างนี้ ฟังก์ชันผลรวม SUM

 PIVOT 
 (SUM(luong) 

กำหนดค่าที่จะโอน

สุดท้าย ค่าจะต้องถูกถ่ายโอนเพื่อรวมผลลัพธ์ นี่จะเป็นส่วนหัวของคอลัมน์ในการสืบค้นข้าม

ในตัวอย่างนี้ เราจำเป็นต้องคืนค่า id_folder 30 และ 45 เท่านั้น ค่าเหล่านี้จะเป็นชื่อคอลัมน์ในตารางใหม่ โปรดจำไว้ว่าค่าเหล่านี้เป็นรายการค่าที่จำกัดของ id_phong และไม่จำเป็นต้องมีค่าทั้งหมด

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

DDL / DML สำหรับตัวอย่าง

หากคุณมีฐานข้อมูลและต้องการลองใช้ตัวอย่างในคู่มือการใช้งาน PIVOT ด้านบน คุณจะต้องใช้ DDL / DML

DDL - ภาษาคำจำกัดความของข้อมูล เป็นคำสั่งสร้างตาราง (CREATE TABLE) สำหรับใช้ในตัวอย่างคำสั่งย่อย 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 - ภาษาการจัดการข้อมูล เป็นคำสั่ง INSERT เพื่อสร้างข้อมูลที่จำเป็นสำหรับตาราง

  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);