'how to transform comma separated column into multiples rows in db2

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?

sqlfunctiondb2transformrows


Solution 1:[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.

Solution 2:[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..

Solution 3:[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

Solution 4:[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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Lennart
Solution 2 programmerBert_makes_u_Happy
Solution 3 Jeff Rudnick
Solution 4

Related Questions