Empty Strings, NULLs and For XML Path I have no control over the source table.
There is a mixture of NULLs and Empty strings.
What I need is to get all users on 1 line and semicolon delimit a list of all their entitlements. I can strip the leading semicolon in later code but the double semicolons and empty string semicolon are killing me.

My table:

EMPLOYEEID | USERNAME | ISPRIMARY | ENTITLEMENTS 
123456     | BSmith   |   Y       | ac-les-all-full;ac-haw-all-full
123456     | BSmith   |   N       | ac-sco-all-full
987654     | ZJones   |   N       | NULL
987654     | ZJones   |   Y       | ac-trn-std-full
987654     | ZJones   |   N       | 
456789     | RTaylor  |   Y       |


I want to return:

USERNAME | ENTITLEMENTS
BSmith   | ;ac-les-all-full;ac-haw-all-full;ac-sco-all-full
ZJones   | ;ac-trn-std-full
RTaylor  | 


My Current SQL:

SELECT
    USERNAME
    ,ENTITLEMENTS = (
        SELECT DISTINCT ';' + ENTITLEMENTS
        FROM [staff_user_data] STS
        WHERE STS.[EMPLOYEEID] = STP.[EMPLOYEEID]
        FOR XML PATH(''))
FROM [staff_user_data] STP
WHERE ISPRIMARY = 'Y'


What I am getting is 

USERNAME | ENTITLEMENTS
BSmith   | ;ac-les-all-full;ac-haw-all-full;ac-sco-all-full
ZJones   | ;;ac-trn-std-full
RTaylor  | ;
This appears to give you what you want.  I added logic where the ENTITLEMENTS are being generated to eliminate rows that are null or blank and wrapped that section in an isnull.

--demo setup
drop table if exists staff_user_data
go
CREATE TABLE staff_user_data (
  EMPLOYEEID INTEGER,
  USERNAME VARCHAR(7),
  ISPRIMARY VARCHAR(1),
  ENTITLEMENTS VARCHAR(31)
);

INSERT INTO staff_user_data
  (EMPLOYEEID, USERNAME, ISPRIMARY, ENTITLEMENTS)
VALUES
  ('123456', 'BSmith', 'Y', 'ac-les-all-full;ac-haw-all-full'),
  ('123456', 'BSmith', 'N', 'ac-sco-all-full'),
  ('987654', 'ZJones', 'N', NULL),
  ('987654', 'ZJones', 'Y', 'ac-trn-std-full'),
  ('987654', 'ZJones', 'N', ''),
  ('456789', 'RTaylor', 'Y', '');

--solution
SELECT
    USERNAME
    ,ENTITLEMENTS = isnull((
        SELECT DISTINCT ';' + ENTITLEMENTS
        FROM [staff_user_data] STS
        WHERE STS.[EMPLOYEEID] = STP.[EMPLOYEEID]
        and ENTITLEMENTS is not null and ENTITLEMENTS &lt;&gt; ''
        FOR XML PATH('')),'')
FROM [staff_user_data] STP
WHERE ISPRIMARY = 'Y'




 USERNAME | ENTITLEMENTS                                     |
|----------|--------------------------------------------------|
| BSmith   | ;ac-les-all-full;ac-haw-all-full;ac-sco-all-full |
| ZJones   | ;ac-trn-std-full                                 |
| RTaylor  |                                                  |