資料庫黑洞

希望能將 A 的 CODE_STR 欄位字串，個別對應 B 的 CODE 找到對應中文 ( CODE_NAME )，達到 C 的動態字串對照表的功能

 

範例語法

CREATE TABLE TB_DATA(
	ID INT,
	CODE_STR VARCHAR(100)
);

--建立中文對照表(如上圖 B)
CREATE TABLE TB_CODE(
	CODE VARCHAR(10),
	CODE_NAME NVARCHAR(10)
);
GO

--寫入資料
INSERT TB_DATA
VALUES(1,'A01|B576'),(2,'B576'),(3,'C|D|E2');
INSERT TB_CODE
VALUES('A01','蘋果'),('B576', '檸檬'),('C', '鳳梨'),('D', '橘子'),('E2', '香蕉');
GO

SELECT * FROM TB_DATA;
SELECT * FROM TB_CODE;

 

步驟 1 : 先將字串切割成單筆資料 ( 此法為遞迴功能，可參考：[MSSQL] CTE 的應用(3) - 遞迴 )

	SELECT ID, CAST(LEFT(CODE_STR, CHARINDEX('|', CODE_STR+'|')-1) AS VARCHAR(999)),
		STUFF(CODE_STR, 1, CHARINDEX('|',CODE_STR+'|'), ''), CODE_STR AS CODE
	FROM TB_DATA a
	UNION ALL
	SELECT ID, CAST(LEFT(CODE_STR, CHARINDEX('|', CODE_STR+'|')-1) AS VARCHAR(999)),
		STUFF(CODE_STR, 1, CHARINDEX('|', CODE_STR+'|'), ''), CODE
	FROM tmp
	WHERE CODE_STR > ''
)
SELECT ID, CODE_SPLIT, CODE FROM TMP ORDER BY ID;

 

步驟 2 : JOIN 中文對照表

	SELECT ID, CAST(LEFT(CODE_STR, CHARINDEX('|', CODE_STR+'|')-1) AS VARCHAR(999)),
		STUFF(CODE_STR, 1, CHARINDEX('|',CODE_STR+'|'), ''), CODE_STR AS CODE
	FROM TB_DATA a
	UNION ALL
	SELECT ID, CAST(LEFT(CODE_STR, CHARINDEX('|', CODE_STR+'|')-1) AS VARCHAR(999)),
		STUFF(CODE_STR, 1, CHARINDEX('|', CODE_STR+'|'), ''), CODE
	FROM tmp
	WHERE CODE_STR > ''
)
SELECT DISTINCT ID, CODE,
	(SELECT b.CODE_NAME + '|'
	FROM TMP a
	JOIN TB_CODE b ON a.CODE_SPLIT = b.CODE
	WHERE a.ID = c.ID
	FOR xml PATH ('')) AS CODE_NAME
FROM TMP c;

 

步驟 3 : 去除掉最後的分割字符，完成

	SELECT ID, CAST(LEFT(CODE_STR, CHARINDEX('|', CODE_STR+'|')-1) AS VARCHAR(999)),
		STUFF(CODE_STR, 1, CHARINDEX('|',CODE_STR+'|'), ''), CODE_STR AS CODE
	FROM TB_DATA a
	UNION ALL
	SELECT ID, CAST(LEFT(CODE_STR, CHARINDEX('|', CODE_STR+'|')-1) AS VARCHAR(999)),
		STUFF(CODE_STR, 1, CHARINDEX('|', CODE_STR+'|'), ''), CODE
	FROM tmp
	WHERE CODE_STR > ''
)
SELECT ID, CODE, LEFT(CODE_NAME, LEN(CODE_NAME) - 1) AS CODE_NAME
FROM (
	SELECT DISTINCT ID, CODE,
		(SELECT b.CODE_NAME + '|'
		FROM TMP a
		JOIN TB_CODE b ON a.CODE_SPLIT = b.CODE
		WHERE a.ID = c.ID
		FOR xml PATH ('')) AS CODE_NAME
	FROM TMP c
) d;

 

參考資料

Turning a Comma Separated string into individual rows

[SQL]將多筆資料合併為一筆顯示(FOR XML PATH)

 

本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權，文章歡迎轉載，請註明出處，謝謝~~~