Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 remove trailing comma from FOR XML PATH output

Author  Topic 

ipisors
Starting Member

39 Posts

Posted - 2015-03-06 : 16:33:26
No matter what I do (I've tried len() and datalength()), I either get an error (with len) or a failure to trim the comma, with datalength.

How can I remove the trailing comma on this output?

I'm getting outputs with trailing commas, of course, on CRSCODES and CLAIMNBRS

The only way I've solved this in the past is in SSIS to pass it to a string and use left(len()) stuff on the string in .NET, but here I'm needing to achieve that directly in SSMS.


with Member_Codes_Claims as
(
select distinct
#temp.membernbr,
#temp.membername,
#temp.dob,
(SELECT
diag.diag + ',' AS [text()] --get "diag" field, might be multiple rows
FROM [datawarehousecms].dbo.CLAIM_DIAGS_V diag
where diag.CLAIMNO=#temp.claimno and diag.diag in(select codes from [thp_reporting].[dbo].[ISAACPISORS_CRSCODES]) --for current claimno, AND only crs codes
FOR XML PATH ('')
) AS 'CRSCODES',

(SELECT
#temp2.claimno + ',' AS [text()] --get "diag" field, might be multiple rows
FROM #temp2
where #temp2.membernbr=#temp.membernbr --get claim no's , potentially multiple, from other temp table
FOR XML PATH ('')
) AS 'CLAIMNBRS'
from #temp
)
--select * from member_codes_claims
select
MEMBERNBR AS 'MEMBERNBR',
MEMBERNAME AS 'MEMBERNAME',
DOB AS 'DOB',
LEFT(CRSCODES,DATALENGTH(CRSCODES)-1) AS 'CRSCODES',
LEFT(CLAIMNBRS,DATALENGTH(CLAIMNBRS)-1) AS 'CLAIM NBR(S)'
from
Member_Codes_Claims
order by membernbr

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-03-06 : 23:26:22
Don't use a trailing comma, use a leading comma, then use STUFF() to remove the first one:
STUFF((SELECT 
',' + diag.diag AS [text()] --get "diag" field, might be multiple rows
FROM [datawarehousecms].dbo.CLAIM_DIAGS_V diag
where diag.CLAIMNO=#temp.claimno and diag.diag in(select codes from [thp_reporting].[dbo].[ISAACPISORS_CRSCODES]) --for current claimno, AND only crs codes
FOR XML PATH ('')
),1,1,'') AS 'CRSCODES',
Go to Top of Page
   

- Advertisement -