假设我们有一个简单的选择查询，它返回如下结果
FirstName LastName PayScale
----------- ------------ ---------------
Craig L 150000
Alice,Lisa simons 100000
所以如果我们有逗号分隔的名字，那么我想要这样的结果
FirstName LastName PayScale
----------- ------------ ---------------
Craig L 150000
Alice simons 100000
Lisa simons 100000
您可以将查询编写为：
DECLARE @employee TABLE (FirstName VARCHAR(100),
LastName VARCHAR(100),
PayScale INT)
INSERT INTO @employee VALUES
('Craig','L',150000) ,
('Alice,Lisa','simons',100000)
SELECT Split.a.value('.', 'VARCHAR(100)') AS FirstName , LastName ,PayScale
FROM (SELECT CAST ('<M>' + REPLACE(FirstName, ',', '</M><M>') + '</M>'
AS XML) AS FirstName , LastName ,PayScale
FROM @employee)
AS A
CROSS APPLY FirstName.nodes ('/M') AS Split(a)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
