sql server 将逗号分隔的值拆分成列 [英] sql server split comma separated values into columns

问题描述

我试图将 csv 拆分为单独的列

I trying to split the csv to individual columns

样本数据

PAR_COLUMN  PERIOD  VALUE       mul_query
----------  ------  ---------   ---------
1           601     10.134542   10.134542
1           602     20.234234   10.134542*20.234234
1           603     30.675643   10.134542*20.234234*30.675643
1           604     40.234234   10.134542*20.234234*30.675643*40.234234
2           601     10.345072   10.345072
2           602     20.345072   10.345072*20.345072
2           603     30.345072   10.345072*20.345072*30.345072
2           604     40.345072   10.345072*20.345072*30.345072*40.345072

预期结果:

PAR_COLUMN  period   value     (No column name)    (No column name)    (No column name)    (No column name)
----------  ------  ---------  ----------------    ----------------    ----------------     ---------------
1           601     10.134542  10.134542            1                   1                   1
1           602     20.234234  10.134542            20.234234           1                   1
1           603     30.675643  10.134542            20.234234           30.675643           1
1           604     40.234234  10.134542            20.234234           30.675643           40.234234
2           601     10.345072  10.345072            1                   1                   1
2           602     20.345072  10.345072            20.345072           1                   1
2           603     30.345072  10.345072            20.345072           30.345072           1
2           604     40.345072  10.345072            20.345072           30.345072           40.345072

我是这样试的.当数据量很大时，它正在工作，但速度很慢.有没有更好的选择.

I tried like this. It is working but very slow when data is large. Is there any better alternative.

declare @sql varchar(max) = ''
set @sql =
';WITH Split_Names 
AS
(
    SELECT PAR_COLUMN,
    mul_query,period,
    CONVERT(XML,''<Names><name>''  
    + REPLACE(mul_query,''*'', ''</name><name>'') + ''</name></Names>'') AS xmlname
      FROM #finals
)
SELECT PAR_COLUMN,
    period,
'


declare @start int =1 ,@count int 
set @count = (select (max(period) - min(period))+1 from #finals)
while @start <= @count
begin 
set @sql +=concat( 'isnull(xmlname.value(''/Names[1]/name[',@start,']'',''float''),1) , ')
 set @start+=1
end
set @sql =left(@sql,len(@sql)-1)
set @sql+= ' FROM Split_Names'

exec( @sql)

注意:问题是不将CSV转换为Individual Rows.我正在尝试将 CSV 转换为单独的 Columns 基本上我正在尝试计算 Value 列中的 RUNNING Multiplication

Note: The question is NOT to convert CSV to Individual Rows. I am trying to convert CSV to indivdual Columns Basically am trying to calculate RUNNING Multiplication in Value column

推荐答案

动态解决此问题，使用DSQL在结果中相应添加更多列.

Dynamically solve this problem, use DSQL to add more columns in the result accordingly.

--create split function
CREATE FUNCTION [dbo].[SO_Split]
(
    @List nvarchar(2000),
    @SplitOn nvarchar(5)
) 
RETURNS @RtnValue table
(

    Id int identity(1,1),
    Value nvarchar(100)
)
AS 
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
    Set @List =Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
    Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))
    Return
END

--below is the dynamic solution for this problem
declare @sql nvarchar(3000) = 'select *'
declare @cnt int = 1
declare @rowNum int = (select max(a) from (select(select max(id) as id_max from dbo.so_split(mul_query,'*')) as a from #test) as b)

while(@cnt <= @rowNum)
begin
    set @sql = @sql + N', ISNULL((select value from dbo.so_split(mul_query,''*'') where id = '+cast(@cnt as nvarchar(5))+N'),''1'')'
    set @cnt = @cnt + 1
end

set @sql = @sql + N' from #test'

exec sp_executesql @sql

结果附在下面.

这篇关于sql server 将逗号分隔的值拆分成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！