We have this query where called a function with 2 values.
SELECT CampoValores, Numero FROM [dbo].[ObtenerDatosPropios] ('RE-00935898','Alvaro')
And this is the results:
But we need to split "CampoValor" in different columns separeated with Semicolon(;)
Something like this:
If it is possible we need to solve this recursively.
Here is an inline approach, provided you have a fixed or maximum number of columns.
Easy to expand or contract... the pattern is pretty clear.
Example
Declare @YourTable table (ID int,SomeCol varchar(max))
Insert Into @YourTable values
(1,'Some;string;with;text')
,(2,'Another;string;with;some;more;text')
Select A.ID
,B.*
From @YourTable A
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
From (Select Cast('<x>' + replace((Select replace(A.SomeCol,';','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as X
) B
Returns
EDIT - For the Function
Select B.*
,Numero
From [dbo].[ObtenerDatosPropios] ('RE-00935898','Alvaro') A
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
From (Select Cast('<x>' + replace((Select replace(A.CampoValores,';','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as X
) B
EDIT 2 - Dynamic
Not tested, but this should work. We're basically doing some dynamic SQL within dynamic SQL.
Declare @SQL varchar(max) = '
Select A.Numero
,Col = ''Pos''+cast(RetSeq as varchar(25))
,B.*
Into #Temp
From [dbo].[ObtenerDatosPropios] ('''+@P1+''','''+@P2+''') A
Cross Apply (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value(''(./text())[1]'', ''varchar(max)'')))
From (Select x = Cast(''<x>'' + replace((Select replace(A.CampoValores,'';'',''§§Split§§'') as [*] For XML Path('''')),''§§Split§§'',''</x><x>'')+''</x>'' as xml).query(''.'')) as A
Cross Apply x.nodes(''x'') AS B(i)
) B
Declare @SQL varchar(max)
Set @SQL = Stuff((Select Distinct '',''+quotename(Col) From #Temp Order By 1 For XML Path('''')),1,1,'''')
Set @SQL = ''
Select ''+@SQL+'',Numero
From (Select Col,RetVal,Numero From #Temp) A
Pivot (max([RetVal]) For [Col] in ('' + @SQL + '') ) p''
Exec(@SQL)
'
--Print @SQL
Exec(@SQL)
