remove trailing comma from FOR XML PATH output

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources