Pivot from ntext column on a ',' comma

Transact-SQL
1

I have a table that has 2 columns, IndexNbr and a ntext column in it. Users are entering data into this table in a specific way that I need to now pivot the data to display in multiple rows based on the ',' (comma).

Example of the data in the column: Acct. 234032498 $25.00, Acct. 592389239 $45.00, Acct. 182038247 $30.00

What I want returned is:

NoteIndex Comment
1 Acct. 234032498 $25.00
1 Acct. 592389239 $45.00
1 Acct. 182038247 $30.00

Any ideas on how to pivot the data based on the comma at the end of each section of text?

2 
Declare @t Table([Declaration] varchar(250));    
Insert Into @t( [Declaration])
Values('Acct. 234032498 $25.00, Acct. 592389239 $45.00, Acct. 182038247 $30.00')

Select t.Declaration  
    From
    (
        Select 
        [Declaration],
        Cast('<X>'+Replace(t.[Declaration],',','</X><X>')+'</X>' As XML) As record

        From @t t
    )x
    Cross Apply
    ( 
        Select fdata.D.value('.','varchar(50)') As Declaration 
        From x.record.nodes('X') As fdata(D)
    ) t
3

Viggneshwar, I made some minor adjustments and all looks good with the exception to the following as this:

Select ded.EMPLOYID as Employee_Id, ded.DEDUCTON as Allotment
, substring(LTRIM(RTRIM(t.Declaration)),1,(CHARINDEX('',LTRIM(RTRIM(t.Declaration)),1)-1)) as Account_Nbr , substring(LTRIM(RTRIM(t.Declaration)),(CHARINDEX('',LTRIM(RTRIM(t.Declaration)),1)+1),8) as Amount
From
(
Select NOTEINDX,
TXTFIELD,
Cast(''+Replace(CAST(t.TXTFIELD AS VARCHAR(250)),',','')+'' As XML) As record

    From SY03900 t
)x
Cross Apply
( 
    Select ltrim(rtrim(fdata.D.value('.','varchar(50)'))) As Declaration 
    From x.record.nodes('X') As fdata(D)
) t

, UPR00500 ded
Where x.NOTEINDX = ded.NOTEINDX

The only issue I have is that on rows greater than 1 the t.declaration column has an extra space in the front of the result. I was thinking that my trim functions would remove the extra space but no luck, any ideas? This is what is being returned:

Employee_Id Allotment Account_Nbr Amount
8800013 BAPCOM Acct. 234032498 25.00
8800013 BAPCOM Acct. 592389239 45.00
8800013 BAPCOM Acct. 182038247 30.00

Not sure if it will show the extra space here but it exists in front of the 2nd and 3rd Row before the word 'Acct.'

4

Change the code Like this

, ltrim(rtrim(substring(t.Declaration,1,(CHARINDEX('$',t.Declaration,1)-1)))) as Account_Nbr