表格规范化（将逗号分隔为各个记录的逗号） [英] Table Normalization (Parse comma separated fields into individual records)

问题描述

我有一张这样的表：

设备

DeviceId   Parts

1          Part1, Part2, Part3
2          Part2, Part3, Part4
3          Part1

我想创建一个表零件，将数据从零件列导出到新表。我将删除零件列后

I would like to create a table 'Parts', export data from Parts column to the new table. I will drop the Parts column after that

预期结果

零件

PartId PartName

  1      Part1
  2      Part2
  3      Part3
  4      Part4

DevicePart

DeviceId PartId

  1      1
  1      2
  1      3
  2      2
  2      3
  2      4
  3      1

我可以在SQL Server 2008中不使用游标？

Can I do this in SQL Server 2008 without using cursors?

推荐答案

- 设置：

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 @Device
values
    (1, 'Part1, Part2, Part3'),
    (2, 'Part2, Part3, Part4'),
    (3, 'Part1')

--Script：

declare @DevicePartTemp table(DeviceId int, PartName varchar(100))

insert @DevicePartTemp
select DeviceId, ltrim(x.value('.', 'varchar(100)'))
from
(
    select DeviceId, cast('<x>' + replace(Parts, ',', '</x><x>') + '</x>' as xml) XmlColumn
    from @Device
)tt
cross apply
    XmlColumn.nodes('x') as Nodes(x)


insert @Part
select distinct PartName
from @DevicePartTemp

insert @DevicePart
select tmp.DeviceId, prt.PartId
from @DevicePartTemp tmp 
    join @Part prt on
        prt.PartName = tmp.PartName

- 结果：

select *
from @Part

PartId      PartName
----------- ---------
1           Part1
2           Part2
3           Part3
4           Part4


select *
from @DevicePart

DeviceId    PartId
----------- -----------
1           1
1           2
1           3
2           2
2           3
2           4
3           1   

这篇关于表格规范化（将逗号分隔为各个记录的逗号）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！