This should work:

DECLARE @Chuff TABLE

(

Sub_Cat_ID INT NOT NULL,

BSNS_IDINT NOT NULL,

BSBS_PRFX VARCHAR(10) NOT NULL,

DO_ID INT NOT NULL

);

INSERT INTO @Chuff (Sub_Cat_ID,BSNS_ID,BSBS_PRFX,DO_ID)

VALUES (13,16,'cntcr',3),

(11,16,'cntcr',3),

(2058,1,'cntcr',3);

DECLARE @Chuff2 TABLE

(

seq INT NOT NULL,

Descr VARCHAR(25) NOT NULL

);

INSERT INTO @Chuff2 (seq,Descr)

VALUES (11,'Sparky'),(13,'Brick layer'),(2058,'Land Surveyor');

WITH Base

AS

(

SELECTC.BSNS_ID,

C1.Descr

FROM@Chuff AS C

INNER

JOIN@Chuff2 AS C1

ONC.Sub_Cat_ID = C1.seq

)

SELECTBusinessID = C.BSNS_ID,

STUFF((SELECT ',' + B.Descr

FROMBase AS B

WHEREC.BSNS_ID = B.BSNS_ID

FOR XML PATH('')), 1, 1, ''),

Prefix = C.BSBS_PRFX,

C.DO_ID

FROM@Chuff AS C

GROUPBY C.BSNS_ID,

C.BSBS_PRFX,

C.DO_ID;

Edit - Read the link in Sean's port for more details