以下查询返回如下所示的结果:
SELECT
ProjectID, newID.value
FROM
[dbo].[Data] WITH(NOLOCK)
CROSS APPLY
STRING_SPLIT([bID],';') AS newID
WHERE
newID.value IN ('O95833', 'Q96NY7-2')
Results:
ProjectID value
---------------------
2 Q96NY7-2
2 O95833
2 O95833
2 Q96NY7-2
2 O95833
2 Q96NY7-2
4 Q96NY7-2
4 Q96NY7-2
使用新添加的STRING_AGG
函数(在 SQL Server 2017 中),如以下查询所示,我可以获得下面的结果集。
SELECT
ProjectID,
STRING_AGG( newID.value, ',') WITHIN GROUP (ORDER BY newID.value) AS
NewField
FROM
[dbo].[Data] WITH(NOLOCK)
CROSS APPLY
STRING_SPLIT([bID],';') AS newID
WHERE
newID.value IN ('O95833', 'Q96NY7-2')
GROUP BY
ProjectID
ORDER BY
ProjectID
Results:
ProjectID NewField
-------------------------------------------------------------
2 O95833,O95833,O95833,Q96NY7-2,Q96NY7-2,Q96NY7-2
4 Q96NY7-2,Q96NY7-2
我希望我的最终输出只有独特的元素,如下所示:
ProjectID NewField
-------------------------------
2 O95833, Q96NY7-2
4 Q96NY7-2
关于如何获得这个结果有什么建议吗?如果需要,请随时从头开始完善/重新设计我的查询。