从逗号分隔的字符串中提取单词到单个行。

Question

我有下表：

(RowNumber int, Names (varchar(50))

​

​

我需要一个update语句或函数，将逗号之间的所有单词分隔成不同的列，如下所示：

​

​

我尝试过以下脚本，但它不能工作，因为在列[Names]中，每行有不同的逗号，不能添加NULL值。

select * 
from [TTT2] mt
cross apply ( select str = mt.[Names] + ',,' ) f1
cross apply ( select p1 = charindex( ',', str )) ap1
cross apply ( select p2 = charindex( ',', str, p1 + 1)) ap2
cross apply ( select p3 = charindex( ',', str, p2 + 1)) ap3
cross apply ( select p4 = charindex( ',', str, p3 + 1)) ap4
cross apply ( select p5 = charindex( ',', str, p4 + 1)) ap5
cross apply ( select p6 = charindex( ',', str, p5 + 1)) ap6
cross apply ( select p7 = charindex( ',', str, p6 + 1)) ap7
cross apply ( select p8 = charindex( ',', str, p7 + 1)) ap8
cross apply ( select p9 = charindex( ',', str, p8 + 1)) ap9
cross apply ( select p10 = charindex( ',', str, p9 + 1)) ap10
cross apply ( select col1 = substring( str, 1, p1-1 )                   
, col2 = substring( str, p1+1, p2-p1-1 )
, Col3 = substring( str, p2+1, p3-p2-1 )
, Col4 = substring( str, p3+1, p4-p3-1 )
, Col5 = substring( str, p4+1, p5-p4-1 )
, Col6 = substring( str, p5+1, p6-p5-1 )
, Col7 = substring( str, p6+1, p7-p6-1 )
, Col8 = substring( str, p7+1, p8-p7-1 )
, Col9 = substring( str, p8+1, p9-p8-1 )
, Col10 = substring( str, p9+1, p10-p9-1 )
          ) ParsedData

请帮我弄些蔬菜：)

谢谢

Answer 1

这是可行的，但有一些限制。我不确定是否需要列总是包含相同的值(基于示例数据，这似乎就是)。我在VegX，A和Z中加入了一个行来说明这个帮助我的希望

--  Get the sample input table
declare     @intable    table
    (RowNumber int, Names varchar(50))
insert into @intable 
values 
(1, 'Veg A, Veg B, Veg C, Veg D'), 
(2, 'Veg A'), 
(3, 'Veg A, Veg B, Veg C'), 
(4, 'Veg X, Veg Y, Veg Z')

-- Limitation : Will work only if list is not greater than 12 vegetables.. If more needed, need to build a query and then execute

-- Step1 : Split the content using string_split 
;with sco as (
select  rownumber, ltrim(Rtrim(Value)) as myvalue
from    @intable
        cross apply string_split(names, ',') t2),
-- Step 2 build col name using dense_rank
sco2 as (
    select  rownumber, myvalue, 'col' + format(dense_rank() over(partition by rownumber order by myvalue), '0') as mycol
    from sco)
-- Final select using PIVOT to put rows in columns
select  * from sco2
PIVOT
(
    min(myvalue)
    FOR mycol in (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12)) as ptv

​

​

Answer 2

1.静态版本

此解决方案将精确地处理8列。

declare @tmp table (RowNumber int, [Names] varchar(max))
insert into @tmp values
(1,'alfaalfa sprouts, bean sprouts , black beans, black-eyed peas, borlotti bean, broad beans, chickpeas, garbanzos'),
(2,'alfaalfa sprouts, bean sprouts'),
(3,'alfaalfa sprouts, bean sprouts , black beans, black-eyed peas')

;WITH Splitted AS 
( 
    SELECT   CAST('<x>' + REPLACE([Names],',','</x><x>') + '</x>' AS XML) AS Names 
    FROM @tmp 
) 
SELECT Names.value(N'/x[1]','varchar(max)') AS Col1 
      ,Names.value(N'/x[2]','varchar(max)') AS Col2 
      ,Names.value(N'/x[3]','varchar(max)') AS Col3 
      ,Names.value(N'/x[4]','varchar(max)') AS Col4 
      ,Names.value(N'/x[5]','varchar(max)') AS Col5 
      ,Names.value(N'/x[6]','varchar(max)') AS Col6 
      ,Names.value(N'/x[7]','varchar(max)') AS Col7 
      ,Names.value(N'/x[8]','varchar(max)') AS Col8 
FROM Splitted;

结果：

​

​

2.动态版本

此解决方案处理的任意数量的列。在本例中，我添加了一个新的蔬菜，以便在结果中出现一个新列(col9)：

declare @col_num int
declare @counter int=1
create table #tmp (RowNumber int, [Names] varchar(max))
insert into #tmp values
(1,'alfaalfa sprouts, bean sprouts , black beans, black-eyed peas, borlotti bean, broad beans, chickpeas, garbanzos, myNewVegetable!'),
(2,'alfaalfa sprouts, bean sprouts'),
(3,'alfaalfa sprouts, bean sprouts , black beans, black-eyed peas')

select @col_num = max(len([Names]) - len(replace([Names],',','')) + 1)  from #tmp

declare @sql nvarchar(max)=''
set @sql  = @sql +';WITH Splitted AS ('
set @sql  = @sql +'SELECT   CAST(''<x>'' + REPLACE([Names],'','',''</x><x>'') + ''</x>'' AS XML) AS Names '
set @sql  = @sql +'FROM #tmp) '
set @sql  = @sql +'SELECT '

while @counter <= @col_num
    begin
        if @counter > 1
            set @sql  = @sql + ','
        set @sql  = @sql +'Names.value(N''/x['+ cast(@counter as varchar(max)) +']'',''varchar(max)'') AS Col' + cast(@counter as varchar(max)) + ' '
        set @counter = @counter + 1
    end
set @sql  = @sql +'FROM Splitted; '
exec(@sql)

结果：

​

​