Light 學習筆記

前言

工作上的需要，匯出報表時需將資料內容轉成資料欄位，故需尋找解法，

且每份報表要轉的資料欄位皆不相同，所以需要動態組成欄位。

查詢Sql JOIN之後的資料如下：

 

 

 

 

 

 

 

想要轉成下列的顯示方式：

 

 

 

 

動手做

準備測試資料

-- 書及自定義欄位資料
CREATE TABLE [dbo].[Book] (
[ISBN] [NVARCHAR](50) NOT NULL
,[BookName] [NVARCHAR](200) NOT NULL
,[CustmerFieldType] [NVARCHAR](200) NULL 
,[CustmerFieldValue] [NVARCHAR](200) NULL 
)
-- 新增資料
INSERT [dbo].[Book] ([ISBN], [BookName], [CustmerFieldType], [CustmerFieldValue]) VALUES ('0001', N'老人與海', N'作者',N'歐内斯特·海明威');
INSERT [dbo].[Book] ([ISBN], [BookName], [CustmerFieldType], [CustmerFieldValue]) VALUES ('0001', N'老人與海', N'出版年',N'1952');
INSERT [dbo].[Book] ([ISBN], [BookName], [CustmerFieldType], [CustmerFieldValue]) VALUES ('0001', N'老人與海', N'自定義類型',N'小說');
INSERT [dbo].[Book] ([ISBN], [BookName], [CustmerFieldType], [CustmerFieldValue]) VALUES ('0002', N'原子習慣', N'作者',N'詹姆斯‧克利爾');
INSERT [dbo].[Book] ([ISBN], [BookName], [CustmerFieldType], [CustmerFieldValue]) VALUES ('0002', N'原子習慣', N'出版年',N'2019');
INSERT [dbo].[Book] ([ISBN], [BookName], [CustmerFieldType], [CustmerFieldValue]) VALUES ('0002', N'原子習慣', N'自定義類型',N'');
INSERT [dbo].[Book] ([ISBN], [BookName], [CustmerFieldType], [CustmerFieldValue]) VALUES ('0003', N'牧羊少年奇幻之旅', N'作者',N'保羅．科爾賀');
INSERT [dbo].[Book] ([ISBN], [BookName], [CustmerFieldType], [CustmerFieldValue]) VALUES ('0003', N'牧羊少年奇幻之旅', N'出版年',N'2021');
INSERT [dbo].[Book] ([ISBN], [BookName], [CustmerFieldType], [CustmerFieldValue]) VALUES ('0003', N'牧羊少年奇幻之旅', N'自定義類型',N'奇幻');

使用PIVOT轉置

SELECT *
	FROM (
		SELECT *
		FROM [dbo].[Book]
	) t 
	PIVOT (
		-- 選擇欄位
		max(t.[CustmerFieldValue])
		-- 設定橫轉直 轉置欄位，只能用[]，不能用單引號''
		FOR [CustmerFieldType] IN ([作者],[出版年],[自定義類型])
	) p	
	ORDER BY p.ISBN

因為工作上的需求，[CustmerFieldType]的欄位不一定一樣，

所以使用動態組成轉置欄位的方式。

DECLARE @columns NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);
BEGIN
	-- 設定橫轉直 轉置欄位
	SET @columns = STUFF((SELECT distinct ',' + QUOTENAME(c.[CustmerFieldType]) 
        FROM [dbo].[Book] c			
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'');
	SET @query = N'SELECT *
	FROM (
		SELECT *
		FROM [dbo].[Book]
	) t 
	PIVOT (
		max(t.[CustmerFieldValue])
		FOR [CustmerFieldType] IN (' + @columns + ')
	) p	
	ORDER BY p.ISBN ';							
	exec sp_executesql @query;
END;

 

 

 

 

 

成功~

參考資料

https://learn.microsoft.com/zh-tw/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/