sql server将逗号分隔的值拆分为列

I trying to split the csv to individual columns

我试图将csv拆分为单个列

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

注意:问题不是将CSV转换为单独行。我试图将CSV转换为单独的列基本上我正在尝试计算值列中的RUNNING Multiplication

4 个解决方案

#1

Dynamically solve this problem, use DSQL to add more columns in the result accordingly.

动态解决此问题,使用DSQL相应地在结果中添加更多列。

--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.

结果如下。

#2

You can roll your own string splitting function as detailed in great depth here by Jeff Moden.

您可以滚动自己的字符串拆分功能,详见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

#3

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

在CROSS APPLY和UDF的帮助下分割值

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

#4

Just a select that transforms the field to an XML so that the numbers can be extracted from it.

只是一个将字段转换为XML的选择,以便可以从中提取数字。

select PAR_COLUMN, PERIOD, VALUE
 ,x.value('/x[1]','float') as mul1
 ,x.value('/x[2]','float') as mul2
 ,x.value('/x[3]','float') as mul3
 ,x.value('/x[4]','float') as mul4
 --,(x.value('/x[1]','float') * x.value('/x[2]','float') * x.value('/x[3]','float') * x.value('/x[4]','float')) as mul_total
from (
  select PAR_COLUMN, PERIOD, VALUE, 
  cast('<x>'+replace(mul_query,'*','</x><x>')+'</x><x>1</x><x>1</x><x>1</x>' as xml) as x 
  from YourTable t
) q;

If just the total of the multiplication is needed, then a subquery can be avoided.
Which could speed it up.
The query below uses XQuery to do the multiplication.

如果只需要乘法的总和,则可以避免子查询。这可以加快速度。下面的查询使用XQuery进行乘法运算。

select PAR_COLUMN, PERIOD, VALUE, 
cast('<x>'+replace(mul_query,'*','</x><x>')+'</x><x>1</x><x>1</x><x>1</x>' as xml).value('x[1]*x[2]*x[3]*x[4]','float') as mul_result
from YourTable t