代码之家 › 专栏 › 技术社区 › Robert

将多个分隔符分隔的字段视为不同的行

sql-server-2005 tsql

Robert · 技术社区 · 8 年前

我继承了一张桌子(谁没有,对吧?)数据如下:

Item          |             Properties        |        Quantity
--------------------------------------------------------------------
Shirt         |  button-down,polo,sleeveless  |          4,5,8

就短期而言,我想创建一个视图,但最终我想在时间允许的情况下将数据导出到表的新版本,并且更像是:

Item          |             Properties        |        Quantity
--------------------------------------------------------------------
Shirt         |            button-down        |            4   
Shirt         |               polo            |            5
Shirt         |             sleeveless        |            8

本质上,采用多个列组(我想还有其他表,其中有多于两列的列具有这种行为),这些列是已知的分隔符分隔的,并将它们拆分为不同的行?收集到的任何其他与此不同的行将像本例中的Item一样在它们之间共享。#逗号在这些类型之间是一致的。

编辑:我使用了答案中给出的函数 How to convert comma separated NVARCHAR to table records in SQL Server 2005? 这是我目前拥有的:

select distinct data.item, tmptbl.[String] from
  data cross apply [ufn_CSVToTable](data.properties, ',') tmptbl ...

这在单列上下文中有效,但将该函数直接应用于第二列(本例中为数量)将生成属性和数量的所有可能组合,对吗?事实上,是的,当我尝试的时候,结果确实是这样。似乎我需要一个游标或类似的东西来有效地将属性[I]|quantity[I]分成单独的行,然后尝试构造它。或者只需选择数据并在应用程序端将其拆分。

3 回复 | 直到 8 年前

TheGameiswar 8 年前

使用中的一个拆分字符串 here ..

如果您确信Properties将始终具有与quantity相同的计数数(我的意思是Properties中有3个值,quantity中有三个值),那么您可以将下面的联接替换为内部联接。。

;With cte
as
(select t.item ,a1.item as 'Properties',row_number() over (order by (select null)) as rownum1
 from #test t
cross apply
[dbo].[SplitStrings_Numbers](proper,',') a1
)
,cte1 as
(
select a2.item as quantity,row_number() over (order by (select null)) as rownum2
 from #test t
cross apply
[dbo].[SplitStrings_Numbers](quantity,',') a2
)
Select c.ite,c.Properties,c1.quantity
from cte c
full join
cte1 c1
on c.rownum1=c1.rownum2

输出:

item    Properties  quantity
Shirt   button-down    4
Shirt   polo           5
Shirt   sleeveless     8

John Cappelletti 8 年前

借助拆分器和交叉应用

Declare @YourTable table (item varchar(50),Properties varchar(50),Quantities varchar(50))
Insert into @YourTable values
('Shirt','button-down,polo,sleeveless','4,5,8')


Select A.item
      ,B.Properties
      ,B.Quantities
 From @YourTable A
 Cross Apply (Select Properties=A.Key_Value
                    ,Quantities=B.Key_Value
               From (Select * from [dbo].[udf-Str-Parse](A.Properties,',')) A
               Left Join (Select * from [dbo].[udf-Str-Parse](A.Quantities,',')) B on A.Key_PS=B.Key_PS 
 ) B

退换商品

item    Properties    Quantities
Shirt   button-down   4
Shirt   polo          5
Shirt   sleeveless    8

自定义项

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--       Select * from [dbo].[udf-Str-Parse]('id26,id46|id658,id967','|')
--       Select * from [dbo].[udf-Str-Parse]('hello world. It. is. . raining.today','.')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select Key_Value = ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End

Robert 8 年前

CREATE FUNCTION [dbo].[ReturnTableOfVarchars]  
  (@IDList varchar(8000))  
    -- allow up to 256 varchar  
    RETURNS @IDTable table (RecordID varchar(256) NOT NULL)  
AS  
  BEGIN  
    DECLARE @IDListPosition int,   
      @ArrValue varchar(8000)  
    SET @IDList = COALESCE(@IDList,'')  
    IF @IDList<>''  
      BEGIN  
        -- add a comma to end of list  
        SELECT @IDList = @IDList+','  
        -- Loop through the comma delimited string list  
        WHILE PATINDEX('%,%',@IDList)<>0  
        BEGIN  
          -- find the position of the first comma in the list  
          SELECT @IDListPosition = PATINDEX('%,%',@IDList)  
          -- extract the string  
          SELECT @ArrValue = LEFT(@IDList, @IDListPosition - 1)  
          INSERT @IDTable (RecordID) VALUES(@ArrValue)  
          -- remove processed string  
          SELECT @IDList = STUFF(@IDList,1,@IDListPosition ,'')  
        END  
      END  
    RETURN  
  END

declare @itemvalues varchar(100) ,@itemcount varchar(100)
set @itemvalues='button-down,polo,sleeveless'
SET @itemcount =' 4,5,8'

select * from dbo.ReturnTableOfVarchars(@itemvalues)
select * from dbo.ReturnTableOfVarchars(@itemcount)

上面的函数从字符串中分割值,您可以根据您的流程插入select或update中的值。