Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

sql server split comma separated values into columns

I trying to split the csv to individual columns

SAMPLE DATA

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

EXPECTED RESULT :

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)

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

The result is attached below. enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...