Recombining Parsed data in SQL

A couple of weeks ago, I posted about using User Defined functions and Cross Apply to parse data in SQL server.  The second part of the import I was working on required me to take that parsed data, perform some manipulations, and then recombine that data.  Surprisingly, I found that putting that data together was much more difficult then pulling it apart in the first place.  After much experimentation, this is what I came up with…

As you may recall from my previous post, I created a Table-Valued function called Parsedata to pull apart the data from a field.  The next step was to join each parsed data value, and join it to another table holding those values as well as an ID for each value.  I then needed to combine the ID’s from that second table.

 My Original Data Looked Like:

 ID  Value
—————
1   ABC, DEF
2   HIJ, KLM
3   NOP, QRS

First, I created a view using the ParseData function:  (This is what I did in my previous blog post)

Create View [dbo].[vDataSplitSource] as
select Table.ID, s.Value from Table
cross apply
dbo.ParseData(Table.Value, ‘,’) s

After parsing the data, I get a view containing:

ID    Value
————–
1     ABC
1     DEF
2     HIJ
2     KLM
3     NOP
3     QRS

That view functions as my source.  My target is a hash table which contains IDs for each value, similar to:

ItemID   Value
—————-
11       ABC
12       DEF
13       HIJ
14       KLM
15       NOP
16       QRS

I create a simple view joining my parsed data to the hash table data:

Create View [dbo].[vJoinedData2] as
select distinct a.ID, b.Value
from vDataSplitSource a inner join HashTable b
on a.Value = b.Value

 Now I have a view containing values from both the view and the table.

For the next step, I created a new Scalar-Valued Function to actually put the data back together.  In this particular case, my result set needed to not only have commas as delimiters, but also needed commas at the beginning and the end of the string as well.  The function requires that you pass the ID for each row as well:

 Create FUNCTION [dbo].[CombineData] ( @c INT )
    RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @p VARCHAR(MAX) ;
               
           SET @p = ‘,’ ;
        SELECT @p = @p + rtrim(ltrim(Value)) + ‘,’
          FROM vJoinedData2
         WHERE Id = @c ;
    RETURN @p
    END

Finally, It was just a matter of calling the function to return the data as I needed it:

select id, cast(dbo.CombineData(id) as nchar(40)) as RetValue
from vJoinedData2

This last data returns:

 ID    RetValue
————–
1     ,11,12,
2     ,13,14,
3     ,15,16,

Unfortunately, I found that the whole process runs very slowly.  I actually used the final statement here as a view, and then joined that view to another view by IDs which proved to be unmanageable when working with larger datasets.  Ultimately, I ended up stuffing the final returned data into a temporary table and joining to that instead.

Anyway…  It look me a while to figure out this whole processes, and most examples I found didn’t exactly conform to my particular requirements, so I hope that someone out there can find this useful.

Thanks for reading!

Jason
Follow JasonBussCFX on Twitter

ABOUT THE AUTHOR

Jason Buss

Jason is a senior application developer with Customer FX.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!