USE TSQL2012;
IF OBJECT_ID('dbo.Team', 'U') IS NOT NULL DROP TABLE dbo.Team;
CREATE TABLE dbo.Team(
ID INT Primary Key,TL VARCHAR(30),Member VARCHAR(200))
INSERT INTO Team VALUES
(1,'Sherman','Sonia,Hilary,Clair'),
(2,'Alvin','Chris,Josie,Joanna'),
(3,'Nancy','Evelyn,Ann,Lena');
select * from Team
ID TL Member
1 Sherman Sonia,Hilary,Clair
2 Alvin Chris,Josie,Joanna
3 Nancy Evelyn,Ann,Lena
SELECT ID,TL ,Property.value('@Name','Nvarchar(100)') AS Member
FROM
(SELECT ID,TL
,CONVERT(XML,'<Root><S Name="'+REPLACE(Member,',','" /><S Name="')+'" /></Root>') AS Member
FROM Team)x
OUTER APPLY Member.nodes('//S') AS T(Property)
ID TL Member
1 Sherman Sonia
1 Sherman Hilary
1 Sherman Clair
2 Alvin Chris
2 Alvin Josie
2 Alvin Joanna
3 Nancy Evelyn
3 Nancy Ann
3 Nancy Lena
SQL Server 分割某表中的字符串字段
最新推荐文章于 2024-08-17 22:30:13 发布