sql server 2012中的回车 [英] carriage return in sql server 2012

问题描述

嘿，我正在使用以下查询来显示以逗号分隔的问题列表.

Hey I am using the following query to display the problem list separated by commas.

SELECT tt.VrNo, STUFF((select ','+ Er1.ErrorDesc 
from ( select * from CallRegErrors )as Main 
left join ErrorMaster ER1 on Main.ErrorCode=ER1.ErrorCode
WHERE (main.VrNo = tt.VrNo)
FOR XML PATH('')) ,1,1,'') AS Problemlist

查询给出像 a,b,c,d 等的输出

query is giving the output like a,b,c,d etc

但我的实际要求是我想在一个新行中显示每个错误描述，
一个
乙
c
d
等

But my actual requirement is I want to display each error description in a new line like,
a
b
c
d
etc

我尝试了以下查询:

SELECT tt.VrNo, STUFF((select char(13)+char(10)+ Er1.ErrorDesc
from ( select * from CallRegErrors )as Main 
left join ErrorMaster ER1 on Main.ErrorCode=ER1.ErrorCode
WHERE (main.VrNo = tt.VrNo)
FOR XML PATH('')) ,1,1,'') AS Problemlist

我也用过

SELECT tt.VrNo,Replace(STUFF((select ','+ Er1.ErrorDesc as [text()] from ( select * from CallRegErrors )as Main left join ErrorMaster ER1 on Main.ErrorCode=ER1.ErrorCodeWHERE (main.VrNo = tt.VrNo)FOR XML PATH('')),1,1,''),',',char(13)+char(10)) AS 问题列表from (select main.VrNo, Er1.ErrorDesc from ( select * from CallRegErrors ) as Main left join ErrorMaster ER1 on Main.ErrorCode=ER1.ErrorCode )as tt由 tt.VrNo 分组但现在使用上述查询后得到由空格而不是逗号分隔的问题列表

SELECT tt.VrNo,Replace(STUFF((select ','+ Er1.ErrorDesc as [text()] from ( select * from CallRegErrors )as Main left join ErrorMaster ER1 on Main.ErrorCode=ER1.ErrorCode WHERE (main.VrNo = tt.VrNo) FOR XML PATH('')),1,1,''),',',char(13)+char(10)) AS Problemlist from (select main.VrNo, Er1.ErrorDesc from ( select * from CallRegErrors )as Main left join ErrorMaster ER1 on Main.ErrorCode=ER1.ErrorCode )as tt group by tt.VrNo but now get the problem list seperated by spaces instead of commas after using the above query

但它没有给出我想要的输出.

but its does not give the output that i want.

请帮忙..

提前致谢

推荐答案

我认为我们需要更多信息才能为您提供帮助.

I think we need more information before we can help you.

我认为您正在尝试将父子关系中子级别的信息格式化为列表.您可能看到过这样的博客 在网络上.

I think you are trying to format the information at the child level in a parent child relationship into a list. You probably saw something like this blog on the web.

但是，您的查询格式不正确.

However, your query is not correctly formatted.

ErrorMaster (Production.ProductCategory) 是父级，CallRegErrors (SUB.ProductCategoryID) 是子级吗?

Is the ErrorMaster (Production.ProductCategory) the parent and CallRegErrors (SUB.ProductCategoryID) the child?

如果是这样，只需将查询更改为那些表名字段名称即可工作.

If so just change the query to those table name field names for it to work.

我对整体结果使用了 REPLACE 函数，将 COMMAS 更改为 CR + LF.

I used the REPLACE function on the overall result to change COMMAS to CR + LF.

-- Sample database
USE AdventureWorks2012
GO

-- Change SQL from www.sqlandme.com for this users problem
SELECT      
    CAT.Name AS [Category],
    REPLACE(STUFF((
        SELECT ',' + SUB.Name AS [text()]                        
        FROM Production.ProductSubcategory SUB
        WHERE SUB.ProductCategoryID = CAT.ProductCategoryID
        FOR XML PATH('') 
        ), 1, 1, '' ), ',', CHAR(13) + CHAR(10))
    AS [Sub Categories]
FROM  Production.ProductCategory CAT

只有在 SSMS 中类型设置为 TEXT 时，您才能在输出窗口中看到回车符.

You can only see carriage returns in the output window when the type is set to TEXT in SSMS.

希望这能解决您的问题.如果没有，请回信提供更多信息！！

I hope this solves your problem. If not, please write back with more information!!

这篇关于sql server 2012中的回车的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！