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