mercredi 13 mai 2015

Remove charecters from concatenation

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 ever 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?

Thanks - (first post)

Aucun commentaire:

Enregistrer un commentaire