我正在使用以下查詢Message根據表中的“Customer_Name”列合并列Customers
SELECT
[Customer_Name],
STUFF((SELECT
', ' LTRIM(RTRIM([Message]))
FROM [dbo].[Customers] t2
WHERE t2.[Customer_Name] = t1.[Customer_Name]
FOR XML PATH ('')), 1, 1, '')
FROM [dbo].[Customers] t1
GROUP BY [Customer_Name]
使用上面的代碼，它們Message被分隔,但我想要一個新行。我嘗試使用CHAR(13) CHAR(10)但我得到#x0D;了合并列似乎是錯誤的。
關于如何修復它的任何想法將不勝感激。
使用@Larnu 幫助回答并發表評論
SELECT
[Customer_Name],
STUFF((SELECT
(CHAR(13) CHAR(10)) LTRIM(RTRIM([Message]))
FROM [Customers] t2
WHERE t2.[Customer_Name] = t1.[Customer_Name]
FOR XML PATH (''),TYPE
).value('(./text())[1]','varchar(MAX)'),1,2,'')
FROM [Customers] t1
GROUP BY [Customer_Name]
uj5u.com熱心網友回復：
您的解決方案使用 xml 序列化。
#x0D; 是char(13)的xml序列化
如果您至少使用 SQL Server 2017，則可以使用STRING_AGG函式
SELECT
[Customer_Name],
STRING_AGG([Message],', ') as [Messages]
FROM [dbo].[Customers] t1
GROUP BY [Customer_Name]
否則你可以添加replace。
SELECT
[Customer_Name],
REPLACE(
STUFF((SELECT
', ' LTRIM(RTRIM([Message]))
FROM [dbo].[Customers] t2
WHERE t2.[Customer_Name] = t1.[Customer_Name]
FOR XML PATH ('')), 1, 2, ''),
'#x0D;',
CHAR(13)) as [Messages]
FROM [dbo].[Customers] t1
GROUP BY [Customer_Name]
uj5u.com熱心網友回復：
防止 XML 物體化的正確方法實際上是xml使用TYPE選項將其生成為資料型別，然后使用.value
DECLARE @sep NVARCHAR(10) = ', '; -- or you can use CHAR(13) CHAR(10)
SELECT
[Customer_Name],
STUFF(
(SELECT @sep LTRIM(RTRIM([Message]))
FROM [dbo].[Customers] t2
WHERE t2.[Customer_Name] = t1.[Customer_Name]
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 1, LEN(@sep), '')
FROM [dbo].[Customers] t1
GROUP BY [Customer_Name]
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/316659.html
標籤：sql sql-server xml 查询语句