MS SQL Server, Hệ thống quản lý cơ sở dữ liệu quan hệ (RDBMS), được sử dụng để lưu trữ và truy xuất dữ liệu. Tính toàn vẹn của dữ liệu, tính nhất quán của dữ liệu và sự bất thường của dữ liệu đóng vai trò chính khi lưu trữ dữ liệu vào cơ sở dữ liệu. Dữ liệu được cung cấp ở các định dạng khác nhau để tạo ra các hình dung khác nhau cho việc phân tích. Với mục đích này, bạn cần xoay vòng (hàng sang cột) và bỏ xoay (cột thành hàng) dữ liệu của mình.

Một PIVOT toán tử quan hệ được sử dụng để chuyển đổi giá trị của nhiều hàng thành giá trị của nhiều cột. Một UNPIVOT toán tử quan hệ được sử dụng để chuyển đổi giá trị của nhiều cột thành giá trị của nhiều hàng.

Trong blog này, chúng ta sẽ thảo luận về việc chuyển đổi giá trị của các hàng thành cột (PIVOT) và giá trị của các cột thành hàng (UNPIVOT) trong MS SQL Server.

Điều kiện tiên quyết

  • Cài đặt MS SQL Server 2012.
  • Tạo cơ sở dữ liệu MovieLens và các đối tượng bảng dựa trên mô hình hóa dữ liệu và dữ liệu mẫu được tải.

Trường hợp sử dụng

Chúng tôi sẽ chuyển đổi dữ liệu hàng thành dữ liệu cột bằng cách sử dụng bảng tạm thời và logic tùy chỉnh, đồng thời điền dữ liệu tổng hợp vào bảng tạm thời.

Mô tả tập dữ liệu

Tập dữ liệu mẫu chứa thông tin về phim và xếp hạng người dùng của nó được sử dụng trong trường hợp sử dụng này. Đối với tập dữ liệu mẫu, vui lòng xem Reference. Mô hình dữ liệu cho tập dữ liệu mẫu như sau:lựa chọn

Cú pháp cho mệnh đề xoay vòng

Cú pháp cho mệnh đề pivot như sau:

SELECT first_column AS <first_column_alias>,
 [pivot_value1], [pivot_value2], ... [pivot_value_n]
 FROM
 (<source_table>) AS <source_table_alias>
 PIVOT
 (
 aggregate_function(<aggregate_column>)
 FOR <pivot_column> IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
 ) AS <pivot_table_alias>;

Tham số hoặc đối số

Các tham số hoặc đối số được sử dụng như sau:

  • first_column: Cột hoặc biểu thức được hiển thị dưới dạng cột đầu tiên trong bảng tổng hợp.
  • first_column_alias: Tiêu đề cột cho cột đầu tiên trong bảng tổng hợp.
  • pivot_value1, pivot_value2... pivot_value_n: Danh sách các giá trị cần xoay.
  • source_table: SELECT câu lệnh cung cấp dữ liệu nguồn cho bảng tổng hợp.
  • source_table_alias: Bí danh cho source_table.
  • aggregate_function: Đại diện cho các hàm tổng hợp, chẳng hạn như SUM, COUNT, MIN, MAXhoặc AVG.
  • aggregate_column: Cột hoặc biểu thức được sử dụng với aggregate_function.
  • pivot_column: Cột chứa các giá trị tổng hợp.
  • pivot_table_alias: Bí danh cho bảng tổng hợp.

Chuyển đổi một hàng thành nhiều cột bằng PIVOT

Một PIVOT toán tử được sử dụng để chuyển các hàng thành cột. Để chuyển đổi một hàng thành nhiều cột, hãy thực hiện như sau.

  • Tìm nạp dữ liệu từ cơ sở dữ liệu bằng cách sử dụng truy vấn dưới đây:
/* Getting table data */
WITH cte_result AS( 
 SELECT
 m.movieid ,m.title ,ROUND(r.rating,0) AS [rating],
 CAST(ROUND(r.rating,0) AS VARCHAR(5))+'_rating' AS [Star]
 FROM [movielens].[dbo].[rating] r
 JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid )
SELECT * FROM (
 SELECT
 movieid AS [MovieId],
 title AS [Movie Name],
 CAST(COUNT(*) AS FLOAT) AS [noofuser],
 CAST(SUM(Rating) AS FLOAT) AS [sumofrating],
 CAST(AVG(Rating) AS FLOAT) AS [avgofrating],
 CASE WHEN star IS NULL THEN 't_rating' ELSE star END [RatingGrade]
 FROM cte_result WHERE MovieId <= 2 GROUP BY ROLLUP(movieid,title,star) )ratingfilter
 WHERE [Movie Name] IS NOT NULL ;
  • Nhận dữ liệu tổng hợp bằng cách sử dụng PIVOT và chuyển đổi một hàng thành nhiều cột bằng cách sử dụng truy vấn dưới đây:
/* Getting aggregated data using Pivot and converting rows to columns */
WITH cte_result AS(
SELECT 
 m.movieid ,m.title ,ROUND(r.rating,0) AS [rating], 
 CAST(ROUND(r.rating,0) AS VARCHAR(5))+'_rating' AS [Star]
FROM [movielens].[dbo].[rating] r 
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid )
SELECT 
[MovieId],
[Movie Name],
[1_rating],
[2_rating],
[3_rating],
[4_rating],
[5_rating],
[t_rating] FROM
(SELECT 
 movieid AS [MovieId] ,
 title AS [Movie Name],
 CAST(COUNT(*) AS FLOAT) AS [noofuser],
 CASE WHEN star IS NULL THEN 't_rating' ELSE star END [RatingGrade]
FROM cte_result GROUP BY ROLLUP(movieid,title,star))ratingfilter
PIVOT (SUM([noofuser]) FOR [RatingGrade] IN ([1_rating],[2_rating],[3_rating],[4_rating],[5_rating],[t_rating]))a 
WHERE [Movie Name] IS NOT NULL ORDER BY movieid

Hàng đơn được chuyển thành nhiều cột được hiển thị trong sơ đồ dưới đây:lựa chọnXếp hạng chuyển đổi của các bộ phim được biểu diễn bằng đồ họa bằng MS Excel như sau:

lựa chọn

Chuyển đổi nhiều hàng thành nhiều cột bằng cách sử dụng PIVOT

Các PIVOT toán tử cũng có thể được sử dụng để chuyển đổi nhiều hàng thành nhiều cột. Để chuyển đổi nhiều hàng thành nhiều cột, hãy thực hiện như sau:

  • Tìm nạp dữ liệu từ cơ sở dữ liệu bằng cách sử dụng truy vấn dưới đây:
/* Getting table data */
WITH cte_result AS(
SELECT 
 m.movieid,
 m.title,
 ROUND(r.rating,0) AS rating,
 u.gender
FROM [movielens].[dbo].[rating] r 
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid
JOIN [movielens].[dbo].[user] u ON u.userid=r.userid
WHERE r.movieid < = 5 )
SELECT movieid,title,CAST(SUM(rating) AS FLOAT) AS rating,CAST(COUNT(*) AS FLOAT) AS nofuser,CAST(AVG(rating) AS FLOAT) avgr,gender FROM cte_result&nbsp;
GROUP BY movieid,title,gender
ORDER BY movieid,title,gender
  • Chọn các hàng để chuyển đổi thành các cột như được hiển thị trong sơ đồ dưới đây:

lựa chọn

Nhiều hàng có thể được chuyển đổi thành nhiều cột bằng cách áp dụng cả hai UNPIVOTPIVOT toán tử cho kết quả.

Sử dụng UNPIVOT toán tử để tìm nạp các giá trị từ rating, nofuseravgr và để chuyển đổi chúng thành một cột có nhiều hàng bằng cách sử dụng truy vấn dưới đây:

/* Getting aggregated data using Unpivot and converting column to row */
WITH cte_result AS(
SELECT 
 m.movieid,
 m.title,
 ROUND(r.rating,0) AS rating,
 u.gender
FROM [movielens].[dbo].[rating] r 
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid
JOIN [movielens].[dbo].[user] u ON u.userid=r.userid
WHERE r.movieid < = 5 )
SELECT movieid,title,gender+'_'+col AS col,value FROM (
SELECT movieid,title,CAST(SUM(rating) AS FLOAT) AS rating,CAST(COUNT(*) AS FLOAT) AS nofuser,CAST(AVG(rating) AS FLOAT) avgr,gender FROM cte_result GROUP BY movieid,title,gender) rt
unpivot ( value FOR col in (rating,nofuser,avgr))unpiv
ORDER BY movieid

Nhiều cột được chuyển đổi thành một cột duy nhất được hiển thị trong sơ đồ dưới đây:lựa chọn

Các PIVOT toán tử được sử dụng trên kết quả thu được để chuyển đổi cột đơn này thành nhiều hàng.

Nhận dữ liệu tổng hợp bằng cách sử dụng PIVOT và chuyển đổi nhiều hàng thành nhiều cột bằng cách sử dụng truy vấn dưới đây:

/* Getting aggregated data using Pivot and converting Multiple rows to Multiple column */
WITH cte_result AS(
SELECT 
 m.movieid,
 m.title,
 ROUND(r.rating,0) AS rating,
 u.gender
FROM [movielens].[dbo].[rating] r 
JOIN [movielens].[dbo].[movie] m ON m.movieid=r.movieid
JOIN [movielens].[dbo].[user] u ON u.userid=r.userid
WHERE r.movieid < = 5 )
SELECT movieid,title,
[M_nofuser],[F_nofuser],
[M_rating],[F_rating],
[M_avgr],[F_avgr] 
FROM 
(
SELECT movieid,title,gender+'_'+col AS col,value FROM (
SELECT movieid,title,CAST(SUM(rating) AS FLOAT) AS rating,CAST(COUNT(*) AS FLOAT) AS nofuser,CAST(AVG(rating) AS FLOAT) avgr,gender FROM cte_result GROUP BY movieid,title,gender) rt
unpivot ( value FOR col in (rating,nofuser,avgr))unpiv )tp
pivot ( SUM(value) FOR col in ([M_rating],[M_nofuser],[M_avgr],[F_rating],[F_nofuser],[F_avgr])) piv 
ORDER BY movieid

Nhiều hàng được chuyển đổi thành nhiều cột được hiển thị trong sơ đồ dưới đây:lựa chọn

Xếp hạng phim đã hoán vị và người dùng của nó được biểu diễn bằng đồ họa bằng MS Excel như sau:

lựa chọn

Và đó là nó!