New feature! Upgrade and increase expert visibility of your issues with Priority Questions.
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?
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')
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.