Database

SQL Server T-SQL Pivot

  1. 說明
  2. 實驗用資料
  3. 相關連結

說明如何使用 SQL Server T-SQL 的 Pivot 以及 Unpivot 設計 View，讓資料的日期格式、圖表與分類能夠預先在資料庫層級完成，系統開發端只需要使用即可 😋

SQL Server Logo

說明

原始的資料表內容。

藉由 PIVOT，按照調查日期分列，包含顯示地點欄位，並且將生物名稱作為欄位，生物名稱中的數值為數量，並且是數量之和 (SUM)，在 PIVOT 當中定義生物名稱要包含的對象，構成欄位。

SELECT p.*
  FROM
  (
	SELECT t1.調查日期, t1.地點, t1.生物名稱, t1.數量
	FROM
	[dbo].[PokeMap] t1
  ) t2
  PIVOT
  (
	SUM([數量])
	FOR [生物名稱] IN ([瑪瑙水母], [角金魚], [大舌貝], [可達鴨], [蚊香蝌蚪], [大鉗蟹])
  ) p
  ORDER BY p.調查日期 asc, p.地點 asc

如果再將記錄人員加入到顯示欄位，則原本的分列依據調查日期，會變成依照調查日期以及記錄人員進行分列。

SELECT p.*
  FROM
  (
	SELECT t1.調查日期, t1.地點, t1.生物名稱, t1.數量, t1.記錄人員
	FROM
	[dbo].[PokeMap] t1
  ) t2
  PIVOT
  (
	SUM([數量])
	FOR [生物名稱] IN ([瑪瑙水母], [角金魚], [大舌貝], [可達鴨], [蚊香蝌蚪], [大鉗蟹])
  ) p
  ORDER BY p.調查日期 asc, p.地點 asc

如果想要 Rename PIVOT 產生的欄位，可以在 SELECT 進行 AS 調整：

SELECT p.調查日期, p.地點, p.瑪瑙水母 '瑪瑙', p.角金魚 '角金'
  FROM
  (
	SELECT t1.調查日期, t1.地點, t1.生物名稱, t1.數量
	FROM
	[dbo].[PokeMap] t1
  ) t2
  PIVOT
  (
	SUM([數量])
	FOR [生物名稱] IN ([瑪瑙水母], [角金魚], [大舌貝], [可達鴨], [蚊香蝌蚪], [大鉗蟹])
  ) p
  ORDER BY p.調查日期 asc, p.地點 asc

實驗用資料

USE DatabaseName
CREATE TABLE [dbo].[PokeMap](
	[調查日期] [date] NULL,
	[地點] [nvarchar](16) NULL,
	[生物名稱] [nvarchar](32) NULL,
	[記錄人員] [nvarchar](16) NULL,
	[數量] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-01-01','瑪瑙水母',15, 'Red')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-01-01','角金魚',2, 'Red')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-01-01','大舌貝',6, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-01-01','可達鴨',1, 'Red')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-01-01','蚊香蝌蚪',6, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-01-01','大鉗蟹',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-01-01','瑪瑙水母',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-01-01','角金魚',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-01-01','大舌貝',0, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-01-01','可達鴨',0, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-01-01','蚊香蝌蚪',11, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-01-01','大鉗蟹',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-01-01','瑪瑙水母',5, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-01-01','角金魚',0, 'Red')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-01-01','大舌貝',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-01-01','可達鴨',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-01-01','蚊香蝌蚪',0, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-01-01','大鉗蟹',0, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-01-01','瑪瑙水母',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-01-01','角金魚',2, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-01-01','大舌貝',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-01-01','可達鴨',5, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-01-01','蚊香蝌蚪',7, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-01-01','大鉗蟹',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-01-01','瑪瑙水母',5, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-01-01','角金魚',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-01-01','大舌貝',3, 'Red')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-01-01','可達鴨',6, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-01-01','蚊香蝌蚪',8, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-01-01','大鉗蟹',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-02-01','瑪瑙水母',9, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-02-01','角金魚',6, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-02-01','大舌貝',5, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-02-01','可達鴨',0, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-02-01','蚊香蝌蚪',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-02-01','大鉗蟹',7, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-02-01','瑪瑙水母',6, 'Red')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-02-01','角金魚',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-02-01','大舌貝',0, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-02-01','可達鴨',0, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-02-01','蚊香蝌蚪',11, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-02-01','大鉗蟹',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-02-01','瑪瑙水母',2, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-02-01','角金魚',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-02-01','大舌貝',8, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-02-01','可達鴨',5, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-02-01','蚊香蝌蚪',6, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-02-01','大鉗蟹',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-02-01','瑪瑙水母',9, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-02-01','角金魚',10, 'Red')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-02-01','大舌貝',5, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-02-01','可達鴨',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-02-01','蚊香蝌蚪',2, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-02-01','大鉗蟹',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-02-01','瑪瑙水母',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-02-01','角金魚',2, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-02-01','大舌貝',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-02-01','可達鴨',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-02-01','蚊香蝌蚪',5, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-02-01','大鉗蟹',5, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-03-01','瑪瑙水母',5, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-03-01','角金魚',2, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-03-01','大舌貝',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-03-01','可達鴨',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-03-01','蚊香蝌蚪',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('湛藍市','2022-03-01','大鉗蟹',2, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-03-01','瑪瑙水母',11, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-03-01','角金魚',2, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-03-01','大舌貝',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-03-01','可達鴨',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-03-01','蚊香蝌蚪',6, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('滿金市','2022-03-01','大鉗蟹',9, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-03-01','瑪瑙水母',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-03-01','角金魚',6, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-03-01','大舌貝',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-03-01','可達鴨',2, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-03-01','蚊香蝌蚪',4, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('煙墨市','2022-03-01','大鉗蟹',8, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-03-01','瑪瑙水母',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-03-01','角金魚',8, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-03-01','大舌貝',6, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-03-01','可達鴨',6, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-03-01','蚊香蝌蚪',4, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('漩渦島','2022-03-01','大鉗蟹',0, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-03-01','瑪瑙水母',6, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-03-01','角金魚',1, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-03-01','大舌貝',0, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-03-01','可達鴨',6, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-03-01','蚊香蝌蚪',3, '')
INSERT INTO [PokeMap] ([地點],[調查日期],[生物名稱],[數量],[記錄人員]) VALUES ('淺蔥市','2022-03-01','大鉗蟹',4, '')

相關連結

SQL Server Integrated Service 初探

SQL Server 閃電般快速查詢指南⚡

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記