DB2 Query -Convert multi values from column to rows

Hi Team

I am using DB2 artisan tool and struck to handle multi values present in columns that are comma(,) separated. I want to convert those column values in separate rows .

For example :

Column 1           Column2
Jan,Feb              Hold,Sell,Buy

Expected Result

Column1                 Column 2
Jan                          Hold
Feb                          Sell
[NULL] or Blank        Buy

Also column values for above will be dynamic and not restricted to just 2 or 3 values and are always comma(,) separated.
Please guide

Please clarify: Do you want

• database tables modified or inserted using
  [list]
• db internal tools (artisan[?] / SQL / ...)
• external tools working on query results
  [/list]
• output files to be operated upon
  [list]
• with which tools
• with which results (store / print / transfer / ...)
  [/list]
  

Hi Rudi . Thank you for the reply. Please find my answers

1.database tables modified or inserted using : DB artisan / Rapid sql
2.output files to be operated upon : Within same tool . Can be used by select query using conversion .

$
$ cat -n col_to_row.sql
     1    connect to sample;
     2    drop table t_data;
     3    create table t_data(col_1 varchar(20), col_2 varchar(20));
     4    insert into t_data (col_1, col_2) values ('Jan,Feb', 'Hold,Sell,Buy');
     5    select * from t_data;
     6
     7    -- Unpivot data using XMLTABLE
     8    WITH x (month, rnum) AS (
     9        SELECT a.mth AS month,
    10               ROW_NUMBER() OVER (PARTITION BY col_1) AS rnum
    11          FROM t_data,
    12               XMLTABLE (
    13                   '$XML_DATA/row/month' passing xmlparse (
    14                        document
    15                        CAST (
    16                            '<row><month id="'||
    17                            REPLACE(col_1, ',', '"></month><month id="')||
    18                            '"></month></row>' AS BLOB
    19                        )
    20                   ) AS "XML_DATA"
    21                   COLUMNS mth VARCHAR(5) path '@id'
    22               ) AS a
    23    ),
    24    y (action, rnum) AS (
    25        SELECT a.act  AS action,
    26               ROW_NUMBER() OVER (PARTITION BY col_1) AS rnum
    27          FROM t_data,
    28               XMLTABLE (
    29                   '$XML_DATA/row/action' passing xmlparse (
    30                        document
    31                        CAST (
    32                            '<row><action id="'||
    33                            REPLACE(col_2, ',', '"></action><action id="')||
    34                            '"></action></row>' AS BLOB
    35                        )
    36                   ) AS "XML_DATA"
    37                   COLUMNS act VARCHAR(5) path '@id'
    38               ) AS a
    39    )
    40    SELECT x.month, y.action
    41      FROM x FULL OUTER JOIN y ON (x.rnum = y.rnum)
    42     ORDER BY x.rnum, y.rnum
    43    ;
    44
$
$
$ db2 -tvmf col_to_row.sql
connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.1.1.1
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE


drop table t_data
DB20000I  The SQL command completed successfully.

create table t_data(col_1 varchar(20), col_2 varchar(20))
DB20000I  The SQL command completed successfully.

insert into t_data (col_1, col_2) values ('Jan,Feb', 'Hold,Sell,Buy')
  Number of rows affected : 1
DB20000I  The SQL command completed successfully.

select * from t_data

COL_1                COL_2
-------------------- --------------------
Jan,Feb              Hold,Sell,Buy

  1 record(s) selected.


WITH x (month, rnum) AS ( SELECT a.mth AS month, ROW_NUMBER() OVER (PARTITION BY col_1) AS rnum FROM t_data, XMLTABLE ( '$XML_DATA/row/month' passing xmlparse ( document CAST ( '<row><month id="'|| REPLACE(col_1, ',', '"></month><month id="')|| '"></month></row>' AS BLOB ) ) AS "XML_DATA" COLUMNS mth VARCHAR(5) path '@id' ) AS a ), y (action, rnum) AS ( SELECT a.act  AS action, ROW_NUMBER() OVER (PARTITION BY col_1) AS rnum FROM t_data, XMLTABLE ( '$XML_DATA/row/action' passing xmlparse ( document CAST ( '<row><action id="'|| REPLACE(col_2, ',', '"></action><action id="')|| '"></action></row>' AS BLOB ) ) AS "XML_DATA" COLUMNS act VARCHAR(5) path '@id' ) AS a ) SELECT x.month, y.action FROM x FULL OUTER JOIN y ON (x.rnum = y.rnum) ORDER BY x.rnum, y.rnum

MONTH ACTION
----- ------
Jan   Hold
Feb   Sell
-     Buy

  3 record(s) selected.


$
$