SQL 如何将一个列分割成多个变量列

4 人关注

我在MSSQL上工作，试图将一个 字符串 列分成多个列。这个字符串列有数字，用分号隔开，比如。

190230943204;190234443204;
然而，有些行的数字比其他行多，所以在数据库中你可以有
190230943204;190234443204;
121340944534;340212343204;134530943204
我见过一些将一列分割成特定数量的列的解决方案，但没有变量列。那些数据较少的列（由逗号分隔的2串字符串，而不是3串）将在第三位出现空值。
有什么想法？如果我必须澄清什么，请告诉我。


           
            
             Joel Coehoorn
            
            ：


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


           
            
             你可以在这里找到你需要的东西：
             
              stackoverflow.com/questions/5342629/...


           
            
             user2522217
            
            ：


           
            
             很抱歉，它们不是逗号，而是分号，用来分隔数据。


           
            
             有可能重复
             
              拆分一列到多列，但数据会有所不同。


           
            
             user2522217
            
            ：


           
            
             我见过这个问题，这不是一个重复的问题，因为那里的人知道他要分成多少列（4），而我不知道。


           
            
             Tony Hopkinson
            
            ：


           
            
             它是否总是2或3列，如果是的话，就使用datalength或者像%;%;%这样的简单解决方案。如果你想知道谁想出了这样的设计，请随便给他一巴掌，事实上，我给他们一个额外的奖励。


           
            
             user2522217
            
            ：


           
            
             哈哈，谢谢，我想最多是10列，但每行的数据可以是1-10个由分号分隔的字符串。


           
            
             你是想修复数据还是只是在应用程序中读取数据？如果是后一种情况，你是用什么语言工作的？


           
            
             user2522217
            
            ：


           
            
             我正试图修复数据，以便以后能更好地分析它。我在MS SQL Server Management Studio的SQL中工作。


           
            
             Tony Hopkinson
            
            ：


           
            
             如果你想分析，那么你要的是行而不是列，就像RandomSeeds的方式一样。在那之后，你会看到某种分割和迭代的方法。

sql


         
          sql-server-2008


         
          tsql


        
         
          
          
           user2522217
          
         
         
          发布于
          
          2013-07-02


          
           已采纳


          
           
            将这些数据分割成独立的列是一个非常好的开始（逗号分隔的值是一个异端）。然而，一个 "可变数量的属性 "通常应该被建模为一对多的
            
             关系
            
            。
           
           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外键。
恭喜你，你已经走上了正确的道路，这就是所谓的规范化。你即将到达第一正常形式。
但是，这些属性应该具有合理的类似性质（即所有的电话号码，或地址，等等）。不要陷入黑暗的一面（又称实体-属性-价值反模式），不要被诱惑把所有的属性扔到同一个表中。如果你能确定几种类型的属性，就把每种类型的属性存储在一个单独的表中。


          
           
            
             
             
              Gordon Linoff
             
            
            
             发布于
             
             2013-07-02


          
           
            
             如果这些都是固定长度的字符串（如问题中），那么你可以相当简单地完成工作（至少相对于其他解决方案）。
            
            select substring(col, 1+13*(n-1), 12) as val
from t join
     (select 1 as n union all select union all select 3
     on len(t.col) <= 13*n.n
如果所有条目的大小相同，这是一个有用的黑客（如果它们的大小不同，就不那么容易）。  然而，要考虑到数据结构，因为分号（或逗号）分隔的列表不是一个很好的数据结构。


           
            
             
              
               user2522217
              
              ：


           
            
             
              
               它们确实是固定长度的字符串。你能说明 "n "和 "t.col "是什么吗？还有，什么是 "val"？


           
            
             
              
               Gordon Linoff
              
              ：


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


          
           
            
             
              如果我是你，我会创建一个简单的函数，像这样用'；'分隔数值。
             
             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]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION [dbo].[fn_Split_List](@List NVARCHAR(512))
RETURNS @ResultRowset TABLE ( [Value] NVARCHAR(128) PRIMARY KEY)
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
比起这样简单的调用。
SET NOCOUNT ON
    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
其结果如下。
Value
1111111
22222222
113113131
3333333
776767676
313131312
54545353
89332131 
总之，这个函数的逻辑并不复杂，所以你可以很容易地把它放在你需要的地方。
注意：对于用';'分隔的数值的多少没有限制，但是函数中有长度限制，如果你需要，可以设置为NVARCHAR（MAX）。
正如我所看到的，在你的例子中，有一些行会导致函数返回空字符串。比如说。
number;number;
number
number
'' (empty string)
要清除它们，只需在上面的语句中添加以下where子句，像这样。