Empty Strings, NULLs and For XML Path

Question

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 | ;

Answer 1

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 <> ''
 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 | |

• Silicon Falcon: Thanks a bunch, that did the trick!