0

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  | ;
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  |                                                  |
1
  • Thanks a bunch, that did the trick! – Silicon Falcon Oct 7 '19 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.