使用SQL Server控制字符串中的值

I want to select a list of items and part numbers for for each item as a string:

我想为每个项目选择一个项目列表和部件号作为字符串:

SELECT top 100 * 
FROM  ii
OUTER APPLY
   (SELECT def, ( ipr.part_number + ',') as prt 
    FROM ipr 
    WHERE ii.item_id = ipr.item_id    
    FOR XML PATH('') ) PN

The error is:

错误是:

[Error Code: 8155, SQL State: S0002] No column name was specified for column 1 of 'PN'.

[错误代码:8155,SQL状态:S0002]没有为'PN'的第1列指定列名。

How can I fix this?

我怎样才能解决这个问题?

2 个解决方案

#1

I think that your whole OUTER APPLY statement generates one XML for both default_part_number and concatenated string, which(the whole XML) doesn't have a name.

我认为你的整个OUTER APPLY语句为default_part_number和连接字符串生成一个XML,其中(整个XML)没有名称。

What you could try to do would be adding alias like this AS PN(TestThis).

您可以尝试做的是添加AS PN(TestThis)之类的别名。

However, I don't think that you're expecting result you're going to get. It would better if you'd give us some example data and expected output. It will be easier for us to solve your problem in that case.

但是,我不认为你期望得到的结果。如果你给我们一些示例数据和预期输出会更好。在这种情况下,我们将更容易解决您的问题。

#2

The combination of XML and STUFF is funny but perfectly fitting to your needs.

XML和STUFF的结合很有趣,但非常适合您的需求。

First you concat your strings with the ', ' in front, then you must return your XML with ", TPYE). You must read the result with ".value()" and use STUFF to replace the first ', '.

首先用前面的','连接你的字符串,然后你必须用“,TPYE”返回你的XML。你必须用“.value()”读取结果并使用STUFF替换第一个','。

You'll find a lot of exampels in the net...

你会在网上找到很多例子......

#1