SQL如何将一个列拆分为多个变量列

Question

我正在处理MSSQL，尝试将一个字符串列拆分为多个列。该字符串列由用分号分隔的数字组成，例如：

190230943204;190234443204;

然而，有些行可能比其他行包含更多的数字，在数据库中可能会出现这种情况。

190230943204;190234443204;
121340944534;340212343204;134530943204

我看到了一些将一列数据分成特定数量的列的解决方案，但没有针对可变列的解决方案。那些数据较少的列（2个由逗号分隔的字符串系列而不是3个）将在第三个位置上有空值。

有什么想法吗？如果需要澄清任何内容，请告诉我。

- user2522217

---

这是糟糕的数据设计。永远不要将逗号分隔的数据存储在列中。希望这里的目的是修复模式。 - Joel Coehoorn

可能是将一列拆分为多列，但数据会变化SQL的重复问题。 - Ken White

它总是会有2或3列吗？如果是这样，只需使用datalength或类似的e.g like %;%;%并采用简单的解决方案。随意抨击首次提出这个设计的人，事实上请代我再打他们一下。 - Tony Hopkinson

哈哈，谢谢。我认为最多只有10列，但每行数据的字符串数量可能会因为分号而有所不同，范围在1到10之间。 - user2522217

如果你想进行分析，那么根据RandomSeeds的方法，你需要行而不是列。之后，你需要考虑某种形式的拆分和迭代操作。 - Tony Hopkinson

Answer 1

将这些数据拆分成单独的列是一个很好的开始（逗号分隔的值是一个异端邪说）。然而，“可变数量的属性”通常应该被建模为一对多关系。

CREATE TABLE main_entity (
  id INT PRIMARY KEY,
  other_fields INT
);

CREATE TABLE entity_properties (
  main_entity_id INT PRIMARY KEY,
  property_value INT,
  FOREIGN KEY (main_entity_id) REFERENCES main_entity(id)
);

entity_properties.main_entity_id是指向main_entity.id的外键。

恭喜您，您正在走上正确的道路，这被称为规范化。您即将达到第一范式。

但要注意，这些属性应该具有相似的性质（即所有电话号码、地址等）。不要陷入黑暗面（也就是实体-属性-值反模式），并且诱惑自己将所有属性都放入同一个表中。如果您可以识别出几种类型的属性，请将每种类型存储在单独的表中。

- RandomSeed

Answer 2

如果这些都是固定长度的字符串（就像问题中一样），那么你可以相对简单地完成这项工作（至少相对于其他解决方案）：

select substring(col, 1+13*(n-1), 12) as val
from t join
     (select 1 as n union all select union all select 3
     ) n
     on len(t.col) <= 13*n.n

如果所有条目的大小相同，则此技巧非常有用（如果它们的大小不同，则不太容易）。但请考虑数据结构，因为分号（或逗号）分隔的列表不是很好的数据结构。

- Gordon Linoff

---

它们确实是固定长度的字符串。您能否澄清一下“n”和“t.col”是什么？还有“val”是什么？ - user2522217

@user2522217 . . . t 是你的表名。col 是包含字符串的列名。n 和 n.n 是子查询及其列的名称。 - Gordon Linoff

Answer 3

如果我是你，我会创建一个简单的函数，用分号分隔数值，像这样：

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'fn_Split_List') AND xtype IN (N'FN', N'IF', N'TF'))
BEGIN
    DROP FUNCTION [dbo].[fn_Split_List]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_Split_List](@List NVARCHAR(512))
RETURNS @ResultRowset TABLE ( [Value] NVARCHAR(128) PRIMARY KEY)
AS
BEGIN
    DECLARE @XML xml = N'<r><![CDATA[' + REPLACE(@List, ';', ']]></r><r><![CDATA[') + ']]></r>'

    INSERT INTO @ResultRowset ([Value])
    SELECT DISTINCT RTRIM(LTRIM(Tbl.Col.value('.', 'NVARCHAR(128)')))
    FROM @xml.nodes('//r') Tbl(Col)

    RETURN
END

GO

那么就简单地这样称呼它：

SET NOCOUNT ON
GO

    DECLARE @RawData TABLE( [Value] NVARCHAR(256))

    INSERT INTO @RawData ([Value] )
    VALUES ('1111111;22222222')
          ,('3333333;113113131')
          ,('776767676')
          ,('89332131;313131312;54545353')

    SELECT SL.[Value]
    FROM @RawData AS RD
    CROSS APPLY [fn_Split_List] ([Value])  as SL

SET NOCOUNT OFF
GO

结果如下：

Value
1111111
22222222
113113131
3333333
776767676
313131312
54545353
89332131 

无论如何，该函数中的逻辑并不复杂，因此您可以轻松地将其放置在任何需要的位置。

注意：使用“;”分隔的值数量没有限制，但是函数中有长度限制，如果需要，可以将其设置为NVARCHAR(MAX)。

编辑：

从我所看到的情况来看，您示例中的某些行会导致函数返回空字符串。例如：

number;number;

will return:

number
number
'' (empty string)

为了清除它们，只需像这样在上述语句中添加以下where子句：

SELECT SL.[Value]
FROM @RawData AS RD
CROSS APPLY [fn_Split_List] ([Value])  as SL
WHERE LEN(SL.[Value]) > 0

- gotqn