Assembling Multiple Values from a Cross Join into a single field.

[pabowen]

I have a requirement to parse apart a field of data that is separated by a semi-colon, to do some work on the individual results (transform based on a case statement), and then to reassemble so that they are in the format of (value; value; value).

I can separate and perform the case statement just fine. I am using a function i wrote called 'stringsplit' where the input values are the field to be split and the separating value. This works very similar to the new 2016+ SQL function called Split_String, where i cross join to create a table of each value.

I separate the values, perform my CASE statement, but that is where i am stuck.

I would like this to be reusable for multiple different fields, so would prefer a function. But am a bit stuck. currently the SplitString function is being called via a CTE, but i have not been able to find a way to reassemble. I even tried cursors, but i can not call the CTE unless it is a part of the cursor. (this is for one time migration purposes, performance is not critical. Yes, I know the evils of Cursors).

As I must perform this on multiple fields I am really looking for this to be reusable.

My intent was to separate and clean the values via a CTE and then to process the values back together by a function that referenced the CTE values. I am now a bit befuddled.

Any ideas?

My Current CTE is costructed as follows:

WITH ERP_Breakdown AS
(
Select 
CASE item
		WHEN 'SAP' THEN 'SAP'
		WHEN 'Oracle' THEN 'Oracle'
		WHEN 'Infor' THEN 'Infor'
		WHEN 'PeopleSoft System' THEN 'PeopleSoft'
		WHEN 'J D Edwards' THEN 'JD Edwards'
		WHEN Null THEN ''
		ELSE 'Other' END as ERP,
ID
From Source.dbo.Account
Cross Apply dbo.SplitString(erp__c, ';')
)

The Cursor I was trying to use as a proof of concept is: (my intention was to eventually feed the cursor into a function and utilize dynamic sql to make it reusable.)

DECLARE @Value as VARCHAR(255)
DECLARE @Result as VARCHAR(255)

DECLARE db_cursor CURSOR FOR 
SELECT ERP 
FROM ERP_Breakdown 
WHERE ID = '0013000000ARZRmAAP'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @Value  

SET @Result = @Value

WHILE @@FETCH_STATUS = 0  
BEGIN  
      SET @Result = CONCAT(@Result, ';', @Value) 
      
      FETCH NEXT FROM db_cursor INTO @Value
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

 

[simian336]

Hey,

Do a google search on "FOR XML PATH" examples... It will let you combine them. I have to do them for people that have multiple phone numbers.

Simi