Row value (with ,) values to column in SQL-CodePudding

I am trying to do this in SQL: data in column is like this need to separate them into new columns.

Create table #TEST4 ( NAME VARCHAR(25) ) 

INSERT INTO #TEST4
VALUES ( 'a,b,c,d,e')
,( 'ax,bde,c,ded,es')

select name  from #TEST4

Expecting result like this, any suggestions will be appreciated.

enter image description here

CodePudding user response：

Using a bit of JSON in concert with a CROSS APPLY

Select Name1 = JSON_VALUE(JS,'$[0]')
      ,Name2 = JSON_VALUE(JS,'$[1]')
      ,Name3 = JSON_VALUE(JS,'$[2]')
      ,Name4 = JSON_VALUE(JS,'$[3]')
 From  #TEST4 A
 Cross Apply (values ( '["' replace(string_escape(NAME,'json'),',','","') '"]' ) ) B(JS)

Results

Name1   Name2   Name3   Name4
a       b       c       d
ax      bde     c       ded

Update: XML Approach

Select Name1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))  -- choose the proper datatype
      ,Name2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
      ,Name3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
      ,Name4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
From  #Test4 A
Cross Apply ( values (cast('<x>'   replace((Select replace(NAME,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>') '</x>' as xml)))  B(xDim)