SQL FOR XML PATH 字串合併遇到CHAR(13) 亂碼的問題

sql

weirdfield 2021-09-13 10:37:20 ‧ 1223 瀏覽

分享至

一個原始資料裡面有含CHAR(13)
使用FOR XML PATH('')進行合併的時候，他會出現亂碼(
)，這個要如何解呢？

合併前的欄位

希望顯示的結果為

目前出現

程式連結

資料庫Microsoft SQL Server 2019
-----------修改------------------
不好意思剛剛改了，如果原始資料，有空值得話，我在合併之後，有加CHAR(10)，他也是會顯示亂碼，這邊要怎麼解呢？

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2021-09-13 11:35:50

最佳解答

CREATE TABLE TableX
(ID varchar(20),
YN varchar(20));

INSERT INTO TableX 
VALUES
('001','y'+CHAR(13)+'n'),
('001',''),
('001','n'+CHAR(13)+'n'),
('002','y'+CHAR(13)+'a'),
('002','y'+CHAR(13)+'y'),
('003','n'+CHAR(13)+'n'),
('003','y'+CHAR(13)+'y');

方法 1 :

SELECT ID,STRING_AGG (YN,CHAR(13)) WITHIN GROUP (ORDER BY ID)
AS StrYN
FROM TableX
WHERE YN <>''
GROUP BY ID

Demo

方法 2 :

WITH CTE_X1 AS (
SELECT ID,STRING_AGG (Replace(YN,CHAR(13),''),'') WITHIN GROUP (ORDER BY ID)
AS StrYN
FROM TableX
GROUP BY ID
),
CTE_X2 AS (
SELECT ID,StrYN,
SUBSTRING(StrYN,1,1)+CHAR(13) AS StrNew,
1 AS NUM
FROM CTE_X1
UNION ALL
SELECT ID,StrYN,
SUBSTRING(StrYN,NUM + 1,1)+CHAR(13) AS StrNew,
NUM + 1 AS NUM
FROM CTE_X2
WHERE LEN(StrYN) - NUM>0
)

SELECT ID,STRING_AGG(StrNew,'') WITHIN GROUP (ORDER BY ID,NUM)
FROM CTE_X2
GROUP BY ID

Demo

回應 2
分享
檢舉

weirdfield iT邦新手 3 級 ‧ 2021-09-13 13:07:53 檢舉

不好意思，另外請問，如果原始資料有重複，把他排除呢？
程式

rogeryao iT邦超人 7 級 ‧ 2021-09-13 13:46:31 檢舉

方法 1 :

WITH CTE_X1 AS (
SELECT ID,YN AS YNTEMP,ROW_NUMBER() OVER (ORDER BY ID) AS NO1
FROM TableX
WHERE YN<>''
),
CTE_X2 AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID,YNTEMP ORDER BY ID,NO1) AS NO2
FROM CTE_X1
),
CTE_X3 AS (
SELECT *
FROM CTE_X2
WHERE NO2=1)

SELECT ID,STRING_AGG (YNTEMP,CHAR(13)) WITHIN GROUP (ORDER BY ID,NO1) AS StrYN
FROM CTE_X3
GROUP BY ID

Demo

方法 2 :

WITH CTE_X1 AS (
SELECT ID,REPLACE(YN,CHAR(13),'') AS YNTEMP,ROW_NUMBER() OVER (ORDER BY ID) AS NO1
FROM TableX
WHERE YN<>''
),
CTE_X2 AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID,YNTEMP ORDER BY ID,NO1) AS NO2
FROM CTE_X1
),
CTE_X3 AS (
SELECT ID,STRING_AGG (YNTEMP,'') WITHIN GROUP (ORDER BY NO1)
AS StrYN
FROM CTE_X2
WHERE NO2=1
GROUP BY ID
),
CTE_X4 AS (
SELECT ID,StrYN,
SUBSTRING(StrYN,1,1)+CHAR(13) AS StrNew,
1 AS NUM
FROM CTE_X3
UNION ALL
SELECT ID,StrYN,
SUBSTRING(StrYN,NUM + 1,1)+CHAR(13) AS StrNew,
NUM + 1 AS NUM
FROM CTE_X4
WHERE LEN(StrYN) - NUM>0
)

SELECT ID,STRING_AGG(StrNew,'') WITHIN GROUP (ORDER BY ID,NUM) AS StrYN
FROM CTE_X4
GROUP BY ID

Demo

登入發表回應