5分鐘學會SQL SERVER行轉列、列轉行,PIVOT操作

2020-12-27 數說數話

正文共：2300 字 3 圖 預計閱讀時間：5 分鐘

我是firewang，用技術讓數據說話，用數據讓你我進化

編輯

本文目錄：

PIVOT語法PIVOT示例UNPIVOT語法UNPIVOT示例PIVOT和UNPIVOT

PIVOT 通過將表達式中的一個列的唯一值轉換為輸出中的多列（即行轉列），來輪替表值表達式。PIVOT 在需要對最終輸出所需的所有剩餘列值執行聚合時運行聚合。與 PIVOT 執行的操作相反，UNPIVOT 將表值表達式的列輪換為行（即列轉行）。

但是需要注意的是，UNPIVOT 並不完全是 PIVOT 的逆操作。PIVOT 執行聚合，並將多個可能的行合併為輸出中的一行。UNPIVOT 不重現原始表值表達式的結果，因為行已被合併。

編輯

實例的數據使用的是和 SQL筆試50題同樣的數據， 使用的平臺是SQLFIDDLE（提供在線資料庫），鑑於近期全球病情的影響，各種網站都有一定機率無法提供服務，本次提供了SQLite資料庫，已存入測試數據。

下載地址（13天內有效）：https://c-t.work/s/1786d12bba3e4c

編輯

編輯

將上述結果新建表 Student_pivot

create table Student_pivot ( sid varchar(10),sname nvarchar(10),sage datetime,ssex nvarchar(10), "語文" int, "數學" int,"英語" int); insert into Student values('01' ， N'趙雷' ， '1990-01-01' ， N'男', 80, 90, 99); insert into Student values('02' ， N'錢電' ， '1990-12-21' ， N'男', 70, 60, 80); insert into Student values('03' ， N'孫風' ， '1990-05-20' ， N'男', 80, 80, 80); insert into Student values('04' ， N'李雲' ， '1990-08-06' ， N'男', 50, 30, 20); insert into Student values('05' ， N'周梅' ， '1991-12-01' ， N'女', 76, 87, null); insert into Student values('06' ， N'吳蘭' ， '1992-03-01' ， N'女', 31, null, 34); insert into Student values('07' ， N'鄭竹' ， '1989-07-01' ， N'女', null, 89, 98); insert into Student values('08' ， N'王菊' ， '1990-01-20' ， N'女', null,null,null);

編輯

然後將這張結果表裡的數據UNPIVOT回去。

編輯

編輯

特別注意哪些成績為空的行記錄都沒有出現！

本文項目地址：

https://github.com/firewang/sql50

參考網址：

https://docs.microsoft.com/zh-cn/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15https://sql50.readthedocs.io/zh_CN/latest/https://github.com/firewang/sql50

相關焦點

  • SQL SERVER 行轉列 PIVOT 用法及解釋
    在資料庫操作過程中，偶爾會使用到《行轉列》查詢；下面介紹Sql server 中常用的行轉列操作；1.語法
  • SQL Server中行轉列和列轉行的那些事兒
    行轉列、列轉行是我們在開發過程中經常碰到的問題。行轉列一般通過CASE WHEN 語句來實現，也可以通過 SQL SERVER 的運算符PIVOT來實現。用傳統的方法，比較好理解。層次清晰，而且比較習慣。但是PIVOT 、UNPIVOT提供的語法比一系列複雜的SELECT…CASE 語句中所指定的語法更簡單、更具可讀性。下面我們通過幾個簡單的例子來介紹一下列轉行、行轉列問題。
  • SQL Server 動態行轉列(參數化表名、分組列、行轉列欄位、欄位值)
    今天跟大家分享SQL Server 動態行轉列（參數化表名、分組列、行轉列欄位、欄位值）的知識。所以在動態列的基礎上再把表、分組欄位、行轉列欄位、值這四個行轉列固定需要的值變成真正意義的參數化，大家只需要根據自己的環境，設置參數值，馬上就能看到效果了（可以轉至：「參數化動態PIVOT行轉列」查看具體的腳本代碼）。行轉列的效果圖如圖1所示：
  • Oracle行轉列函數Pivot簡單用法
    因為工作中經常需要excel或者報表輸出，了解到pivot函數，查了部分資料，實在不夠詳盡，便想自己寫一篇。pivot了解不深，只寫寫簡單用法。
  • MySQL資料庫的分組操作,語句拼接,列轉行操作
    本文介紹MySQL資料庫的分組操作，語句拼接，列轉行操作。說明：僅是MySQL5.7中存在，5.6和8.0都沒有，查看sql_modeselect @@sql_mode;在帶有group by 子句的select中，select後的條件列(非主鍵列)，要麼是group by 後面的列，要麼需要在函數中
  • 【SQL】行轉列
    但是有的時候為了方便分析，需要把行形式的數據展開成列形式的。本文主要就是介紹幾種行轉列的方法，以及我自己由淺入深的思考。SELECT <非透視的列>, [第一個透視的列] AS <列名稱>, [第二個透視的列] AS <列名稱>, ...
  • SQL Server 2014 可更新聚集列存儲索引
    相比行存儲索引，使用列存儲索引有如下好處：　　·首先對於聚合、掃描、分組等大查詢類操作僅僅需要讀取選擇的列，對於需要Join多個表的星型結構等場景的查詢性能提升尤其明顯;　　·其次是列索引可以更新，並且每個表中只需要一個聚集列索引即可，簡化了維護操作;　　·列索引由於是按列存儲，同一列中數據類型是一樣的，因此可以更容易的實現更高壓縮比;　　·列存儲的表由於非常高的壓縮比率
  • 如何使用 SQL Server FILESTREAM 存儲非結構化數據?
    可以通過WIN32 API對這些單獨的文件進行操作。T-SQL語句適用。通過FILESTREAM存儲在文件系統中的對象已經取消了VARBINARY (MAX)列的2GB文件大小限制。可以通過執行腳本5從具有VARBINARY (MAX) FILESTREAM列的表中獲取記錄。select * from[Test_FILESTREAM].[dbo].[Fajlovi]腳本5.
  • Excel – 一列轉多列,多列轉一列,一個「=」搞定
    且不說在各種案例中順帶提到的技巧，單獨開篇講的，就有以下這些：Excel快速將一列數據排列成m行*n列Excel indirect 函數(1) - 將一列數據排列成m行*n列Excel 一維錶轉二維表（填坑貼）Excel 如何將多列數據轉換成一列
  • 對比Excel學習SQL:對列和行的操作
    >一、對列的查詢    01 查詢單個列    03 查詢所有列    04 對列去重二、對列的值排序    01 升序排列    02 降序排列    03 多個列排序三、對行的篩選    01 單條件篩選
  • EXCEL中一列（行）轉多行多列或多行多列轉一列（行）
    1.一列轉多列多行（或多行多列）①先列後行，比如一列數據轉為四列多行。=OFFSET($A$1,ROW(A1)-1+COLUMN(A1)*4-4,)&""2.一行轉多列多行（或多行多列
  • SQL Server應用程式的高級Sql注入
    SQL運行的典型的操作是「查詢」，它是可以讓資料庫返回「查詢結果記錄集」的語句集合。SQL語句可以修改資料庫的結構(用數據定義語言"DDL")和操作資料庫裡的數據(用數據操作語言"DML")。我們在這裡著重討論Transact-SQL(交互式SQL)，應用於SQL-Server的SQL一種方言(非標準SQL)。如果攻擊者可以插一系列的SQL語句進入應用程式的數據查詢時，Sql注入攻擊就可能發生。
  • 快速對行、列、單元格進行操作
    Excel2003及以下版本最大支持65536行，256列，而Excel2007及以上版本最大支持1048576行，16384列。Excel的單元格定義是列標加行標。舉個例子，A1單元格，其中A為列標，1為行標，代表第一行第一列的單元格。 今天我們就來學習如何對行、列、單元格進行快速操作。
  • 神奇的 SQL → 為什麼 GROUP BY 之後不能直接引用原表中的列?
    為什麼 GROUP BY 之後不能直接引用原表（不在 GROUP BY 子句）中的列 ？莫急，我們慢慢往下看。SQL 模式MySQL 伺服器可以在不同的 SQL 模式下運行，並且可以針對不同的客戶端以不同的方式應用這些模式，具體取決於 sql_mode 系統變量的值。
  • SQL Server 安裝步驟
    本文通過圖文並茂的形式給大家介紹了SQL server 2016 安裝步驟，非常不錯，具有參考借鑑價值，需要的朋友參考下吧。
  • NET開發-SQL語句在SQL Server中查詢全部列、指定列和前n條數據
    查詢所有列表在上圖中看到表中所有的行數據都查詢出來了，並且每行都包括所有的列。在這裡使用了*符號，該符號屬於通配符，*符號用在select語句後面，表示所有行和列，而select關鍵字表示查詢的意思。select *表示查詢所有的行和列數據，fr om關鍵表示要在哪個表中查詢數據，fr om後面跟表名。
  • 資料庫sql 學習
    基礎部分1.insert into 語句 用於向表格中插入新的行    insert into 表名稱 (列1，列2，.) values （值1，值2，.）>    select 列名稱, 列名稱  from表名稱    select *from 表名稱    select name,sex from user5.distinct 語句 去重複值    select distinct 列名稱 from 表名稱    select distinct name from user
  • 如何將SQL Server中的表變成txt文件
    說明 Microsoft SQL Server 6.5 中的 bcp 實用工具不支持大容量複製到包含 sql_variant 或 bigint 數據類型的表。 data_file 大容量複製表或視圖到磁碟（或者從磁碟複製）時所用數據文件的完整路徑。當將數據大容量複製到 SQL Server 時，此數據文件包含將複製到指定表或視圖的數據。
  • mysql常用sql語句總結
    ;update 表名 set列1=新值1 ,列2=新值2Where expr;(不加影響所有行);delete from 表名 where expr;select 列1,列2,.列n from 表名 where expr;select * from tmp where name is not null;mysql> select
  • SQL Server 2014
    解壓「cn_sql_server_2014_X64.iso」64位系統解壓「cn_sql_server_2014_X64.iso」。右擊「cn_sql_server_2014_X64.iso」點擊「解壓到cn_sql_server_2014_X64.iso」。