- 设置：
declare @Device table(DeviceId int primary key, Parts varchar(1000))declare @Part table(PartId int identity(1,1) primary key, PartName varchar(100))declare @DevicePart table(DeviceId int, PartId int)insert @Devicevalues (1, 'Part1, Part2, Part3'), (2, 'Part2, Part3, Part4'), (3, 'Part1')
- 脚本：
declare @DevicePartTemp table(DeviceId int, PartName varchar(100))insert @DevicePartTempselect DeviceId, ltrim(x.value('.', 'varchar(100)'))from( select DeviceId, cast('<x>' + replace(Parts, ',', '</x><x>') + '</x>' as xml) XmlColumn from @Device)ttcross apply XmlColumn.nodes('x') as Nodes(x)insert @Partselect distinct PartNamefrom @DevicePartTempinsert @DevicePartselect tmp.DeviceId, prt.PartIdfrom @DevicePartTemp tmp join @Part prt on prt.PartName = tmp.PartName- 结果：
select *from @PartPartId PartName----------- ---------1Part12Part23Part34Part4select *from @DevicePartDeviceId PartId----------- -----------11121322232431
