sql server split comma separated values into columns
Solution 1
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
Solution 2
You can roll your own string splitting function as detailed in great depth here by Jeff Moden.
For posterity purposes, the final code is:
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
Once you have this function, you can pull out the relevant data using a pivot
table:
select PAR_COLUMN
,PERIOD
,VALUE
,mul_query
,[1]
,[2]
,[3]
,[4]
from(select f.PAR_COLUMN
,f.PERIOD
,f.VALUE
,f.mul_query
,s.ItemNumber
,s.Item
from @finals f
cross apply dbo.DelimitedSplit8K(f.mul_query,'*') s
) as d
pivot
(
max(Item)
for ItemNumber in([1],[2],[3],[4])
) as pvt
Pரதீப்
My original name is Thanga Pradeep and I am a C#/Sql Server Developer Best answer I ever wrote in SO(based on complexity not on votes) Calculation in Sql Server Some of my answers I enjoyed writing Simplify MS SQL Statements Generate a sequnce number for every 3 rows in SQL Difference between filtering queries in JOIN and WHERE? How to drop all tables from a database with one SQL query? SQL Server Coalesce data set Also 44th to get Sql Server Gold Badge I learned many things from below Sql Server gurus Aaron Bertrand Marc_S Martin Smith gbn
Updated on June 17, 2022Comments
-
Pரதீப் almost 2 years
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 IndividualRows
. I am trying to convertCSV
to indivdualColumns
Basically am trying to calculate RUNNING Multiplication inValue
column