空字元串、NULL 和 XML 路徑

我無法控制源表。NULL 和空字元串混合在一起。我需要的是讓所有使用者在 1 行上，並用分號分隔他們所有權利的列表。我可以在以後的程式碼中去掉前導分號，但是雙分號和空字元串分號正在殺死我。

我的桌子：

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       |

我想返回：

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

我目前的 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'

我得到的是

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

這似乎給了你你想要的。我添加了生成邏輯ENTITLEMENTS以消除空行或空白行並將該部分包裝在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  |                                                  |

引用自：https://dba.stackexchange.com/questions/250468