How do I get rid of a trailing comma?
HF 128, HF 48, SF 58,
My code looks like this:
SET NOCOUNT ON
DECLARE @Host_Bill nvarchar(8),@Bill_SC nvarchar(8),@SC nvarchar(1),@Companion nvarchar(25), @Similar nvarchar(25)
DECLARE @BillSC nvarchar(250),@SimCom nvarchar(250),@Companions nvarchar(25), @Similars nvarchar(25)
SELECT DISTINCT [Host Bill] INTO #names FROM tbl_SC
CREATE TABLE #final (Host_Bill nvarchar(8), Companion nvarchar(25), Similar nvarchar(25) )
DECLARE curtmp CURSOR FOR SELECT [Host Bill] FROM #names
OPEN curtmp
FETCH NEXT FROM curtmp INTO @Host_Bill
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @BillSC=”
SET @SimCom=”
SET @Companions=”
SET @Similars=”
DECLARE curnames CURSORFOR
SELECT [Bill SC], SC, Companion, Similar FROM tbl_SC
WHERE [Host Bill]=@Host_Bill
OPEN curnames
–Perform the first Fetch and store the values in variables.
–Note the variables are in the same order as the columns in the Select statement
FETCH NEXT FROM curnames
INTO @Bill_SC,@SC,@Companion,@Similar
–Check @@FETCH STATUS to see if there are any more rows to fetch
WHILE (@@FETCH_STATUS = 0)
BEGIN
If @SC = ‘S’
SET @Similars=@Similars+@BillSC+CAST(@Bill_SC AS nvarchar(250))+’, ‘
IF @SC = ‘C’
SET @Companions=@Companions+@BillSC+CAST(@Bill_SC AS nvarchar(250))+’, ‘
–This is executed as long as the previous fetch succeeds
FETCH NEXT FROM curnames
INTO @Bill_SC,@SC,@Companion,@Similar
END
INSERT INTO #final VALUES(@Host_Bill,@Companions,@Similars)
CLOSE curnames
DEALLOCATE curnames
FETCH NEXT FROM curtmp INTO @Host_Bill
END
CLOSE curtmp
DEALLOCATE curtmp
SELECT * FROM #final
DROP TABLE #names
DROP TABLE #final