How to Transform Comma Separated Column into Multiples Rows in Db2

DB2 SQL How to split a column into potentially many rows?

Try this:

DB2 for LUW

/*
WITH TAB (ID, PROFILE) AS 
(
VALUES 
  ('BOB',  'BM        BS        DM        FM        IC        IC6       IL        IM        JN101     MM        XC        XM        XR')
, ('BILL', 'ZZ        XY')
)
*/
SELECT T.ID, V.TOK
FROM TAB T
, xmltable
(
'for $id in tokenize($s, " +") return <i>{string($id)}</i>' 
passing T.PROFILE as "s" 
columns 
  tok varchar(10) path '.'
) V;

dbfiddle example.

DB2 for LUW & IBM i

IBM i doesn't support (at least up to 7.4) FLWOR XQuery expressions, so you need to construct an XML document.

SELECT T.ID, V.TOK
FROM 
  TAB T
, XMLTABLE 
(
'$doc/d/i'
PASSING XMLPARSE(DOCUMENT '<d><i>' || regexp_replace(trim(T.PROFILE), '  +', '</i><i>') || '</i></d>') as "doc"
COLUMNS 
  TOK VARCHAR(10) PATH '.'
) V;

Turning a Comma Separated string into individual rows

You can use the wonderful recursive functions from SQL Server:

Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4

How to split comma delimited data from one column into multiple rows

If you are on SQL Server 2016 or better, you can use OPENJSON() to split up the code values instead of cumbersome string operations:

SELECT t.Employee_FullName,
       Code = LTRIM(j.value),
       Hours = MAX(CASE j.[key] 
                   WHEN 0 THEN RegularTime
                   WHEN 1 THEN DoubleTime
                   WHEN 2 THEN Overtime END)
  FROM dbo.MyTable AS t
  CROSS APPLY OPENJSON('["' + REPLACE(t.Code,',','","') + '"]') AS j
  GROUP BY t.Employee_FullName, LTRIM(j.value);

Example db<>fiddle

Convert Comma Separated column value to rows

try this

 SELECT A.[id],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [id],  
         CAST ('<M>' + REPLACE([string], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

refer here

http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html

SQL concatenate rows into one field (DB2)

Try to modify query accordingly to the below script

 SELECT 
    ID, 
    SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',keyword))) as VARCHAR(1024)), 3) AS "keyword concatenated"
    FROM table.keywordTbl 
    GROUP BY ID

How to split a string value based on a delimiter in DB2

This is what i tried and it fetched me effective result. Hence sharing with all.

select column_name, substr(column_name,1,locate('-',column_name)-1), 
substr(column_name,locate('-',column_name)+1,
length(substr(column_name,locate('-',column_name)+1))) from 
table_name where column_name is not null and column_name!='' 
and column_name like '%-%'