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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…