Remove characters from concatenation

Question

I have a table in with the following layout:

    CREATE TABLE dbo.tbl (
    Ten_Ref VARCHAR(20) NOT NULL,
    Benefit VARCHAR(20) NOT NULL
);

INSERT INTO dbo.tbl (Ten_Ref, Benefit)
VALUES ('1', 'HB'),
       ('1', 'WTC'),
       ('1', 'CB'),
       ('2', 'CB'),
       ('2', 'HB'),
       ('3', 'WTC');

I then run this code to perform a transform and concatenation (I need all the benefit information in one field'

with [pivot] as

(
SELECT Ten_Ref
,[HB] = (Select Benefit FROM tbl WHERE t.Ten_Ref = Ten_Ref and Benefit = 'HB')
,[CB] = (Select Benefit FROM tbl WHERE t.Ten_Ref = Ten_Ref and Benefit = 'CB')
,[WTC] = (Select Benefit FROM tbl WHERE t.Ten_Ref = Ten_Ref and Benefit = 'WTC')
/*Plus 7 more of these*/

FROM tbl as t

GROUP BY Ten_Ref
)

select  p.ten_Ref
        /*A concatenation to put them all in one field, only problem is you end up with loads of spare commas*/
        ,[String] = isnull (p.HB,0) + ',' + isnull (p.cb,'') + ',' + isnull (p.wtc,'')

from [pivot] as p

My problem is not every ten_ref has all of the Benefits attached.

Using this code, where there is a gap or NULL then I end up with loads of double commas e.g 'HB,,WTC'

How can I get it so it is only one comma, regardless of the amount of benefits each tenancy has?

Stephan · Accepted Answer · 2015-05-13T17:17:45.293

Are you looking for something like this?

SELECT  A.Ten_Ref,
        STUFF(CA.list,1,1,'') list
FROM tbl A
CROSS APPLY(
                SELECT ',' + Benefit
                FROM tbl B
                WHERE A.Ten_Ref = B.Ten_Ref
                ORDER BY Benefit
                FOR XML PATH('')
            ) CA(list)
GROUP BY A.ten_ref,CA.list

Results:

Ten_Ref              list
-------------------- ------------------
1                    CB,HB,WTC
2                    CB,HB
3                    WTC

Or if you really want to use pivot and manually concatenate, you could do this:

SELECT  Ten_Ref,
        --pvt.*,
        ISNULL(HB + ',','') + ISNULL(CB + ',','') + ISNULL(WTC + ',','') AS list
FROM tbl
PIVOT
(
    MAX(Benefit) FOR Benefit IN([HB],[CB],[WTC])
) pvt

Thank you Stephen. This solved it. I based my original solution on an answer in this thread: http://stackoverflow.com/questions/24470/sql-server-pivot-examples — spench, May 14 '15 at 08:13

Remove characters from concatenation

1 Answers1