表规范化（将逗号分隔的字段解析为单个记录）

- 设置：

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