SQL Server T-SQL Pivot
2022-09-30
說明如何使用 SQL Server T-SQL 的 Pivot 以及 Unpivot 設計 View，讓資料的日期格式、圖表與分類能夠預先在資料庫層級完成，系統開發端只需要使用即可 😋
說明
原始的資料表內容。
藉由 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, '')