sql server split comma separated values into columns

Question

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

you're dynamically converting a field to xml, then splitting that xml apart. it's ALWAYS going to be slow, because the operation has to be repeated for every row in your table. You'd be better off fixing your table structure so it doesn't do this in the first place. Use a proper normalized design. — Marc B, Sep 08 '16 at 15:02
Possible duplicate of [T-SQL split string](http://stackoverflow.com/questions/10914576/t-sql-split-string) — Chris Pickford, Sep 08 '16 at 15:03
N.B. Using the dupe question link, you can `CROSS JOIN` to the function passing the column to split. — Chris Pickford, Sep 08 '16 at 15:04
SQL Server isn't a great tool to be splitting strings. Much better would be to do the heavy-lifting in .NET, Java, etc., and then import normalized data. — Tim Biegeleisen, Sep 08 '16 at 15:04
@MarcB Basically the Mul_query column is generated by me through another query. Am trying to perform running multiplication in Value column — Pரதீப், Sep 08 '16 at 15:46
Then you should rethink your logic. Why waste all this time/effort to UNDO what this other query had to do especially to build that csv list? Keep the data SEPARATE, especially when you still need to process the components individually. It's like buying a cake at the store with the idea that you can separate it back into milk/eggs/flour sugar. That's WAY more work than just getting a jug of milk, carton of eggs, etc... separately in the first place. — Marc B, Sep 08 '16 at 15:47
@MarcB Faster methods in calculating Running multiplication has approximation issues due to the use of LOG and EXP functions. — Pரதீப், Sep 08 '16 at 15:49
@ChrisPickford - THe duplicate you have tagged to this question is to convert `CSV` to `Rows`. But am looking to convert `CSV` to `Columns` — Pரதீப், Sep 08 '16 at 18:38
assuming 4 columns, use built in function parsename. Change the . to a ~ to a * to a . then change the ~ back to a . after the parsename. Example: http://stackoverflow.com/questions/39390993/how-to-split-string-into-multiple-in-sql-server/39391818#39391818 — xQbert, Sep 08 '16 at 18:42

Dance-Henry · Accepted Answer · 2016-09-10T00:11:44.487

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.

This is a very good solution. Could you please generate column name dynamically also ? LIke Col1,Col2,Col3...... — Rashami Ranjan Biswal, Oct 24 '19 at 12:47

iamdave · Answer 2 · 2016-09-09T09:19:09.633

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

@Prdp This function does exactly what you need if you actually think what you can do with the help you are being given. I have updated the answer to provide the columns as you require them. If you have more delimited values, you will need to add more numbered columns. — iamdave, Sep 09 '16 at 09:20

score 0 · Answer 3 · answered Sep 08 '16 at 15:18

With a little help from a CROSS APPLY and an UDF to split values

Declare @YouTable table (PAR_COLUMN int,PERIOD int,VALUE decimal(18,6), mul_query varchar(250))
Insert Into @YouTable values
(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')

Select A.PAR_COLUMN
      ,A.PERIOD
      ,A.VALUE
      ,Pos1=IsNull(B.Pos1,1)
      ,Pos2=IsNull(B.Pos2,1)
      ,Pos3=IsNull(B.Pos3,1)
      ,Pos4=IsNull(B.Pos4,1)
      ,Pos5=IsNull(B.Pos5,1)
      ,Pos6=IsNull(B.Pos6,1)
 From  @YouTable A
 Cross Apply (Select * from [dbo].[udf-Str-Parse-Row](A.mul_query,'*')) B

Returns

PAR_COLUMN  PERIOD  VALUE       Pos1        Pos2      Pos3      Pos4    Pos5    Pos6
1           601     10.134542   10.134542   1         1         1   1   1
1           602     20.234234   10.134542   20.234234 1         1   1   1
1           603     30.675643   10.134542   20.234234 30.675643 1   1   1
1           604     40.234234   10.134542   20.234234 30.675643 40.234234   1   1
2           601     10.345072   10.345072   1   1   1 1 1
2           602     20.345072   10.345072   20.345072 1 1   1   1
2           603     30.345072   10.345072   20.345072 30.345072 1   1   1
2           604     40.345072   10.345072   20.345072 30.345072 40.345072   1   1

The UDF

CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse-Row]('John Cappelletti',' ')
--       Select * from [dbo].[udf-Str-Parse-Row]('id26,id46|id658,id967','|')

Returns Table 

As

Return (
    SELECT Pos1 = xDim.value('/x[1]','varchar(250)')
          ,Pos2 = xDim.value('/x[2]','varchar(250)')
          ,Pos3 = xDim.value('/x[3]','varchar(250)')
          ,Pos4 = xDim.value('/x[4]','varchar(250)')
          ,Pos5 = xDim.value('/x[5]','varchar(250)')
          ,Pos6 = xDim.value('/x[6]','varchar(250)')
          ,Pos7 = xDim.value('/x[7]','varchar(250)')
          ,Pos8 = xDim.value('/x[8]','varchar(250)')
          ,Pos9 = xDim.value('/x[9]','varchar(250)')
    FROM (Select Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML) as xDim) A

It should be dynamic plus this is same as my approach – Pரதீப் Sep 08 '16 at 17:44 — Pரதீப், Sep 08 '16 at 17:44

sql server split comma separated values into columns

3 Answers3

Linked

Related