CODEWITHSUNDEEP
sql-server
acesta
acesta

Reputation: 79

Concat the values in a string with 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'.

How can I fix this?

Upvotes: 0

Views: 67

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

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

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 ', '.

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

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

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.

What you could try to do would be adding alias like this 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.

Upvotes: 1

Related Questions