how to transform comma separated column into multiples rows in db2

Question

I have the following table (the number of the references is variable):

Id | FK_ID| Reference |
-----------------------
1    2100   GI2, GI32
2    2344   GI56

And I need the following result:

Id | FK_ID| Reference |
-----------------------
1    2100   GI2 
2    2100   GI32
3    2344   GI56

Is there any short way to transform the data like this using DB2?

Kienan

That's worst DB design and this is what happens when you don't normalize. – Eastman

no its not db design it is required in ETL i have to write a fucntion to breakdown these kind of rows. so i need the sql. can you help me in it? – Kienan

Does it have to be SQL. Can't you write a translation program to do this? – Plain

no it has to be sql or sql function in db2. – Kienan

the other day i concatenated multiple rows in db2 by using these functions xmlserialize() xmlagg() xmltext() but now i have to do the reverse with incoming values – Kienan

Answer 1

You really should not be storing data like this. Fortunately, there is a way to undo the damage with recursive SQL, something along these lines:

WITH unpivot (lvl, id, fk_ref, reference, tail) AS (  
  SELECT 1, id, fk_ref,     
         CASE WHEN LOCATE(',',reference) > 0 
              THEN TRIM(LEFT(reference, LOCATE(',',reference)-1))
              ELSE TRIM(reference) 
         END,    
         CASE WHEN LOCATE(',',reference) > 0 
              THEN SUBSTR(reference, LOCATE(',',reference)+1)    
              ELSE '' 
         END  
  FROM yourtable  
  UNION ALL  
  SELECT lvl + 1, id, fk_ref,     
         CASE WHEN LOCATE(',', tail) > 0 
              THEN TRIM(LEFT(tail, LOCATE(',', tail)-1))    
              ELSE TRIM(tail) 
         END,    
         CASE WHEN LOCATE(',', tail) > 0 
              THEN SUBSTR(tail, LOCATE(',', tail)+1)    
              ELSE '' 
         END
  FROM unpivot 
  WHERE lvl < 100 AND tail != '')
  SELECT id, fk_ref, reference FROM unpivot

PS. Not tested.

Herbartian

thanks but this recursive sql is giving me this error DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098. – Kienan

So you have some invalid object (possibly a view) in your database that can't be revalidated. Since you don't provide any details, that's as much as I can tell. – Herbartian

this is what i did with my exact tabel and column name WITH unpivot (lvl ,inventory_data_id, reference, tail) AS ( SELECT 1, inventory_data_id, CASE WHEN LOCATE(',',group_id) > 0 THEN TRIM(LEFT(group_id, LOCATE(',',group_id)-1)) ELSE TRIM(group_id) END, CASE WHEN LOCATE(',',group_id) > 0 THEN SUBSTR(group_id, LOCATE(',',group_id)+1) ELSE '' END – Kienan

FROM test.comma_target where inventory_data_id =1321 UNION ALL SELECT lvl + 1, inventory_data_id, CASE WHEN LOCATE(',', tail) > 0 THEN TRIM(LEFT(tail, LOCATE(',', tail)-1)) ELSE TRIM(tail) END, CASE WHEN LOCATE(',', tail) > 0 THEN SUBSTR(tail, LOCATE(',', tail)+1) ELSE '' END FROM unpivot WHERE lvl < 100 AND tail != '') SELECT id, inventory_data_id, reference FROM unpivot; – Kienan

and this is the error i got 19:42:47 [WITH - 0 row(s), 0.000 secs] 1) [Error Code: -206, SQL State: 42703] DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=ID, DRIVER=4.15.82. 2) [Error Code: -727, SQL State: 56098] DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-206;42703;ID, DRIVER=4.15.82 – Kienan

You are selecting the non-existent column ID (SELECT id, inventory_data_id...) from the CTE unpivot. – Herbartian

thanks for the support it truly was helpful. i have just one problem left it is working fine if i restrict the inventory data id to one id but it fails if i do not restrict it for one inventory data id. – Kienan

and hey mustaccio do you use skype i wud like to add u for any help in future that wud be so nice of you . – Kienan

Answer 2

i did something similar with posstr(trim(ROW, '[DELIMITER]')), LENGTH .. like:

Table
NAME
---------------------
Martin, Example

SELECT         
    CASE WHEN POSSTR(NAME, ',') > 0
         THEN SUBSTR(TRIM(NAME), POSSTR(TRIM(NAME), ',') + 1), LENGTH(TRIM(NAME)) - POSSTR(TRIM(NAME),',')))
         ELSE 'ERROR'
    END
FROM ...
UNION ALL
.. 

Result: Example

but not tested,.. there might then be empty resultsets, they must be deletede afterwards..

Hesperus

Answer 3

I posted a generic solution for this type of transform under Split comma separated entries to rows

To view results for this sample data, replace the session data with:

INSERT INTO session.sample_data SELECT 1, 2100, 'GI2,GI32' FROM sysibm.sysdummy1;
INSERT INTO session.sample_data SELECT 2, 2344, 'GI56' FROM sysibm.sysdummy1;

Results:

ID  OTHERID DATA
1   2100    GI2
1   2100    GI32
2   2344    GI56

Lithophyte

Although you link a post where you answer to the same question, you should post your code here, and explain a little more what it does. – Debutant

Answer 4

1st Off i don't know why so many people say you shouldn't be storing data this/that way etc when you are doing ETL. They clearly don't understand what an ETL developer actually does for a job. Which is they rarely control the format of the data they receive but have to make it behave better when delivered to its destination. Ok enough venting: Here is what i suggest.

I would take the delimited list and turn it into a Values clause for a dynamic SQL Statement to select from or use it to dynamically create a table or view to use... the possibilities after getting the pivot are many and easy. Here is a small code snippet that you could easily embed in a Stored Proc... This assumes a recent version of DB2 (hopefully last 4/5 years)....

declare sDelimitString as Varchar(500);
declare sValues as Varchar(1000);
set sDelimitString = 'Data 1,Data 2,Data 3';
set sValues = ''' Values((' || Replace((sDelimitString,',','''),(''') || '))'' as PIVOT_DATA(SOME_COLUMN_NAME) ';  
declare cCur Cursor with return for aStmt;
set sSQL = Select PIVOT_DATA.SOME_COLUMN_NAME FROM ' || sValues;
prepare aStmt from sSQL;
open cCur;

The idea is to use the values statment like "Select C1 From (Values(1),(2),(3)) as T1(C1)" which pivots. You could even make this part of a larger SQL statement that has your full select in it and dynamically return results, insert the data into a tmp table, or dynamically create a view etc...

Now DB2 is just weird with Dynamic cursors vs oracle, sql server or pretty much any other database. In my experience the declare cCur line has to be the last "Declare" statement in the stored proc otherwise procs won't compile and nowhere on google does it tell you why.... This was painful and shouldn't be but i've found the same pain many times in db2 in other areas. Again once you have the pivot you can do anything with it. And the sSQL doesn't have to be a select statement it could be a command to dynamically build a temp table or view to pull from ... options are limitless after the pivot. Best of luck... I know its late.. but i wasn't a big fan of the solutions or commentary here.

Herzig