• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

Trimming A Trailing Comma

I have the following query:
SELECT DISTINCT
	Table4.Name,
	(SELECT DISTINCT
		ltrim(rtrim(Table2.PCost_Center)) + ','
	FROM 
		Table2 
	where 
		Table1.Company_Code = Table2.Company_Code
		and Table1.Job_Number = Table2.Job_Number
		AND Table2.PCost_Center <> ''
	FOR XML PATH('')) AS Cost_Centers,
	(SELECT  DISTINCT
		ltrim(rtrim(Table2.PCode)) + ','
	FROM 
		Table2 
	where 
		Table1.Company_Code = Table2.Company_Code
		and Table1.Job_Number = Table2.Job_Number
		AND Table2.PCode <> ''
		FOR XML PATH('')) AS PCodes,
	Table1.Job_Description,
	Table1.Job_Number,
	Table3.Alpha_Field AS Site_ID
FROM 
	Table1
	LEFT OUTER JOIN Table2
		ON Table1.Company_Code = Table2.Company_Code 
		AND Table1.Job_Number = Table2.Job_Number
	LEFT OUTER JOIN Table3
		ON Table1.Company_Code = Table3.Company_Code 
		AND Table1.Job_Number = Table3.Job_Number
		AND Table3.User_Def_Sequence = '000003'
	LEFT OUTER JOIN Table4
		ON Table1.Company_Code = Table4.Company_Code 
		AND Table1.Customer_Code = Table4.Customer_Code 
WHERE 
	(NOT (Table1.Customer_Code IS NULL OR Table1.Customer_Code = '')) 
	and (Table4.Company_Code = 'ENG' or Table4.Company_Code = 'INC')

Open in new window

The sub queries which produces Cost_Centers and PCodes produces a value that always ends with a comma (unless the result is null).  How can I trim off the trailing comma?
0
Clif
Asked:
Clif
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you could do:
;with data as ( <yourcurrentselect> )
SELECT Name
   , LEFT(COST_CENTERS, LEN(COST_CENTERS)-1) cost_centers
   , LEFT(PCodes, LEN(PCodes)-1) PCodes
   ... etc .
  FROM data

Open in new window

0
 
ClifAuthor Commented:
That was perfect.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now