sql server 将逗号分隔的值拆分成列 [英] sql server split comma separated values into columns
本文介绍了sql server 将逗号分隔的值拆分成列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图将 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屋!
查看全文