how to transform comma separated column into multiples rows in db2

by

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.

More Related Contents:

  1. Difference between language sql and language plpgsql in PostgreSQL functions
  2. DB2 Comma Separated Output by Groups
  3. Excel function to make SQL-like queries on worksheet data?
  4. How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
  5. Sql Server deterministic user-defined function
  6. What is the difference between function and procedure in PL/SQL?
  7. Does DB2 have an “insert or update” statement?
  8. Call dynamic SQL from function
  9. INNER JOIN in UPDATE sql for DB2
  10. Get previous and next row from rows selected with (WHERE) conditions
  11. Solution to “cannot perform a DML operation inside a query”?
  12. Function for week of the month in mysql
  13. Pivoting in DB2
  14. Regular Expressions in DB2 SQL
  15. Functions with variable number of input parameters
  16. How to resolve SQL0418N Error
  17. Function return sys_refcursor call from sql with specific columns
  18. Functions vs Stored Procedures
  19. DB2 Query to retrieve all table names for a given schema
  20. How to check db2 version
  21. Query to get only numbers from a string
  22. How to create a Calendar table for 100 years in Sql [closed]
  23. Identity column value suddenly jumps to 1001 in sql server [duplicate]
  24. A SQL Query to select a string between two known strings
  25. Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL [duplicate]
  26. How does a Recursive CTE run, line by line?
  27. SQL poor stored procedure execution plan performance – parameter sniffing
  28. SQL for ordering by number – 1,2,3,4 etc instead of 1,10,11,12
  29. RODBC Temporary Table Issue when connecting to MS SQL Server
  30. Postgresql query between date ranges

Leave a Comment