SQLTeam.com | Weblogs | Forums

Pivot from ntext column on a ',' comma


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?

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  
        Cast('<X>'+Replace(t.[Declaration],',','</X><X>')+'</X>' As XML) As record

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


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
Cast(''+Replace(CAST(t.TXTFIELD AS VARCHAR(250)),',','')+'' As XML) As record

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

, UPR00500 ded

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


Change the code Like this

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