Remove comma in first latter - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Remove comma in first latter

Author

Topic

msbsam
Starting Member

2 Posts

Posted - 2013-04-19 : 03:55:12

Hi this is my query and output. I need to remove first letter comma

Declare @packagesList varchar(2000)=''

SELECT @packagesList = COALESCE(@packagesList + ',', '')+ jpc.PackageName FROM tblInvoiceItem invItem
inner join tblJobPackage jpc on jpc.PackageId=invItem.ProductId1
inner join tblJobPosting jp on jp.JobId=invItem.ProductId2
WHERE invItem.ProductId2=10773 AND ItemType=1 AND
(jp.JobId=jp.MultiLocationRefJobID OR jp.MultiLocationRefJobID is null)
AND invItem.ProductId1<>29

SELECT @packagesList

----------Output---------------

,General job advertising,Specialist job advertising,Passive candidate search

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 04:32:33

I think you want to get comma separated packageNames...

Declare @packagesList varchar(2000)=''

SELECT @packagesList = @packagesList + ',' + jpc.PackageName
FROM tblInvoiceItem invItem
inner join tblJobPackage jpc on jpc.PackageId=invItem.ProductId1
inner join tblJobPosting jp on jp.JobId=invItem.ProductId2
WHERE invItem.ProductId2=10773 AND ItemType=1 AND
(jp.JobId=jp.MultiLocationRefJobID OR jp.MultiLocationRefJobID is null)
AND invItem.ProductId1<>29

SELECT STUFF(@packagesList, 1, 1, '') -- Removing first comma from the list

Refer the following link to get CSV List from a column
http://blog.sqlauthority.com/2012/09/14/sql-server-grouping-by-multiple-columns-to-single-column-as-a-string/
--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-04-19 : 05:48:09

quote:
Originally posted by msbsam

Hi this is my query and output. I need to remove first letter comma

Declare @packagesList varchar(2000)='' <-- take away the definition because the coalesce thing needs @packagesList as NULL...

SELECT @packagesList = COALESCE(@packagesList + ',', '')+ jpc.PackageName FROM tblInvoiceItem invItem
inner join tblJobPackage jpc on jpc.PackageId=invItem.ProductId1
inner join tblJobPosting jp on jp.JobId=invItem.ProductId2
WHERE invItem.ProductId2=10773 AND ItemType=1 AND
(jp.JobId=jp.MultiLocationRefJobID OR jp.MultiLocationRefJobID is null)
AND invItem.ProductId1<>29

SELECT @packagesList

----------Output---------------

,General job advertising,Specialist job advertising,Passive candidate search

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources