关于 sql:Table Normalization(将逗号分隔的字段解析为单独的记录)

Table Normalization (Parse comma separated fields into individual records)

我有一张这样的桌子：

设备

1
2
3
4
5

DeviceId Parts

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

我想创建一个表 \\’Parts\\’，将 Parts 列中的数据导出到新表。之后我将删除 Parts 列

预期结果

零件

1
2
3
4
5
6

PartId PartName

1 Part1
2 Part2
3 Part3
4 Part4

设备部件

1
2
3
4
5
6
7
8
9

DeviceId PartId

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

我可以在 SQL Server 2008 中不使用游标来执行此操作吗？

相关讨论

你试过什么了。这里需要的一个提示是一个带有 partID(Int, Identity(1,1)),partname 的表，并使用 select distinct 插入到该表中。之后，一个 Join 将为您提供第二个表..
到目前为止，我已经尝试过游标，但我不喜欢这个解决方案，我觉得应该有更好的方法来做到这一点。

— 设置：

1
2
3
4
5
6
7
8
9

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’)

–脚本：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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

— 结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

您将需要一个 Tally 表来完成此操作，而无需使用光标。

按照说明在此处创建计数表：Jeff Moden 的计数表

此脚本会将表放入您的 Temp 数据库，因此您可能想要更改 “Use DB” 语句

然后您可以运行下面的脚本将设备和部件的细分插入到临时表中。然后，您应该能够通过部件名称加入您的部件表(以获取 ID)并插入到新的 DevicePart 表中。

1
2
3
4
5
6
7
8

SELECT *,
–substring(d.parts, 1, t.n)
SUBSTRING(d.parts, t.n, charindex(‘, ‘, d.parts + ‘, ‘,t.n) – t.n) ‘Part’
INTO #devicesparts
FROM device d
CROSS JOIN tally t
WHERE t.n < (SELECT MAX(len(parts))+ 1 FROM device)
AND SUBSTRING(‘, ‘ + d.parts, t.n, 1) = ‘, ‘

如果每个设备有最大零件数，是的，可以在没有光标的情况下完成，但这很复杂。

基本上，为 PartID 字符串中的每个可能索引创建一个表(或视图或子查询)，其中包含一个 DeviceID 和一个 PartID 列。这可以通过使用 fn_split 或您选择的其他方法使 PartID 列计算列来完成。从那里您对该表执行多个自联合，每个 PartID 列的自联合中都有一个表。 self-UNION 中的每个表只有一个 PartID 列包含在该表的查询的选择列表中。

看看使用 fn_Split 从逗号分隔值创建表变量。
然后，您可以使用它来驱动插入。

编辑：实际上，我认为您可能仍然需要一个光标。留下这个答案以防 fn_Split 有帮助。

Table Normalization (Parse comma separated fields into individual records)

猜你喜欢