The below SQL strings together data

[FranS37]

The below SQL strings together data from multiple rows, separated by a comma. I need to separate those values with carriage return. I can't make it work with suggestions I'm finding on the web. Any ideas?


SELECT DISTINCT p1.RequestLink,
CONVERT(VARCHAR(255),REPLACE((SELECT CONVERT(VARCHAR(30),ApprovalType) + ',' AS 'data()'
FROM Approval p2
WHERE p2.RequestLink = p1.RequestLink
AND (PersonLink IS NOT NULL)
ORDER BY p2.RequestLink
FOR XML PATH('')) + '$', ',$', '')) AS Type
FROM Approval p1
GROUP BY RequestLink

 

[TamarGranor]

Try replacing ',' in

<pre>
CONVERT(VARCHAR(255),REPLACE((SELECT CONVERT(VARCHAR(30),ApprovalType) + ',' AS 'data()'
</pre>

with CHAR(13) or CHAR(13) + CHAR(10).

Tamar

 

[FranS37]

Thank you for the response.

Your suggestions is one I found just doing an on line search, but got the below results:

CSR&#x0D; REQUEST&#x0D; CHECKLIST&#x0D; $

using

CONVERT(VARCHAR(255),REPLACE((SELECT CONVERT(VARCHAR(30),ApprovalType) + CHAR(13) + CHAR(10) AS 'data()'

 

[TamarGranor]

&#x0D; is the way LF (CHAR(10)) is represented in HTML/XML. Have you tried displaying the result somewhere other than SSMS?

I guess a good question is what you want to do with this result.

Tamar

 

[FranS37]

I've only viewed it in SSMS. I want to use the data in an SAP Business Objects report.

Thanks.

 

[FranS37]

What did work was a Replace function in BI - replace commas with Char(13):

Replace ([fieldname];",";Char(13))