SQL Server TSQL 開發技巧- 將資料庫的字串分解後_直接轉換成欄位 - PIVOT 與 SPLIT

tsql_pivot_split.md

---------------------------------------
-- 將資料庫的字串分解後，直接轉換成欄位
---------------------------------------
/*
mystring
------------------------
AAA_BBBB_CC
DDD_EEEE_FF


--結果
1	2	3
AAA	BBBB	CC
DDD	EEEE	FF

*/
---解法，利用傳統函數將字串分解，並且搭配NEWID讓每一組字串有獨立代號
CREATE FUNCTION [dbo].[fnSplitString] 
(   @uid uniqueidentifier,
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(uid uniqueidentifier,id int identity,splitdata NVARCHAR(MAX) 
) 
BEGIN 
   
    DECLARE @start INT, @end INT 
    SELECT  @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE   @start < LEN(@string) + 1 BEGIN 
        IF  @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (uid,splitdata)  
        VALUES(@uid,SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
		delete from @output where splitdata=''

    END 
    RETURN 
END
GO

-- 驗證
select * from [dbo].[fnSplitString](newid(),'AAA_BBBB_CC','_')

uid					id	splitdata
3C6334E4-09B4-48C9-853F-B1B9056B016D	1	AAA
3C6334E4-09B4-48C9-853F-B1B9056B016D	2	BBBB
3C6334E4-09B4-48C9-853F-B1B9056B016D	3	CC

-- 測試資料表
create table tblString
(mystring varchar(1024))
go

--新增驗證資料
insert into tblString(mystring) values('AAA_BBBB_CC'),('DDD_EEEE_FF');

--查詢資料
select * from tblString
go

--結果
mystring
AAA_BBBB_CC
DDD_EEEE_FF

--使用函數分解
SELECT uid,id,splitdata 
FROM tblString cross apply [dbo].[fnSplitString](newid(),mystring,'_')

--結果
uid					id	splitdata
D4A2885D-16FD-4F6E-934E-7E945EB2B236	1	AAA
D4A2885D-16FD-4F6E-934E-7E945EB2B236	2	BBBB
D4A2885D-16FD-4F6E-934E-7E945EB2B236	3	CC
18EA0508-634F-4235-90D5-2FC85139047D	1	DDD
18EA0508-634F-4235-90D5-2FC85139047D	2	EEEE
18EA0508-634F-4235-90D5-2FC85139047D	3	FF

--使用樞紐
SELECT t.[1],t.[2],t.[3] 
FROM   (SELECT uid,id,splitdata FROM tblString cross apply [dbo].[fnSplitString](newid(),mystring,'_')) as SftSales 
PIVOT( MAX(splitdata)  --使用彙總函數
       FOR id IN ([1],[2],[3]) ) t --資料集別名

--結果
1	2	3
AAA	BBBB	CC
DDD	EEEE	FF