我有表tableA如下：
+--------+----------------------+
| Id | Ref |
+--------+----------------------+
| 1 | RN1-102,RN2-103 |
| 2 | RN1-106 |
| 3 | RN2-203 |
| 4 | NULL |
| 5 | RN1-104|,RN2-107 |
| 6 | RN1-101,RN2-105 |
| 7 | RN1-100,RN2-109 |
+--------+----------------------+
我需要一个输出（tableA的Ref与众不同）：
+--------------------+
| Distinct Ref data |
+--------------------+
| RN1-100 |
| RN1-101 |
| RN1-102 |
| RN1-104 |
| RN1-106 |
| RN2-103 |
| RN2-105 |
| RN7-107 |
| RN2-109 |
| RN2-203 |
+--------------------+
我尝试了以下查询：
select distinct Ref from tableA
请帮帮我..
试试这个
SELECT distinct Split.a.value('.', 'VARCHAR(100)') REF
FROM (select ID,Cast ('<M>'
+ replace(Replace(REF, ',', '</M><M>'),'&','&')
+ '</M>' AS XML) AS Data from #Table) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句