SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Remove comma in first latter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

msbsam
Starting Member

Sri Lanka
2 Posts

Posted - 04/19/2013 :  03:55:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/19/2013 :  04:32:33  Show Profile  Reply with Quote
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

Edited by - bandi on 04/19/2013 05:58:49
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 04/19/2013 :  05:48:09  Show Profile  Visit webfred's Homepage  Reply with Quote
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.

Edited by - webfred on 04/19/2013 05:51:07
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000