dynamic PIVOT query with SQL Server -

i have following table account

accountid            calcul   1                    27+23+12 4                    5+9+12 7                    7+12+20 

i looking following table accounttemp

accountid                accountcode      1                    27     1                    23     1                    12     4                    5     4                    9     4                    12      7                    7     7                    12     7                    20 

you this:

create split function this:

create function [dbo].[split] (     @string nvarchar(4000),     @delimiter nchar(1) ) returns table  return  (     split(stpos,endpos)      as(         select 0 stpos, charindex(@delimiter,@string) endpos         union         select endpos+1, charindex(@delimiter,@string,endpos+1)             split             endpos > 0     )     select 'id' = row_number() on (order (select 1)),         'data' = substring(@string,stpos,coalesce(nullif(endpos,0),len(@string)+1)-stpos)     split ) go 

then query this:

select     tbl.accountid,     split.accountcode     @tbl tbl     cross apply     (         select             cast(calc.data int) accountcode                     dbo.split(tbl.calcul,'+') calc     ) split 

output:

accountid    accountcode 1            27 1            23 1            12 4            5 4            9 4            12 7            7 7            12 7            20 

reference:

• split string table using t-sql
• cross apply explained