Home Man
Search
Today's Posts
Register

Post questions about C, C++, Java, SQL, and other programming languages here.

DB2 Query -Convert multi values from column to rows

Tags
programming

Login to Reply

 
Thread Tools Search this Thread
# 1  
Old 02-10-2018
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 :
Code:
Column 1           Column2
Jan,Feb              Hold,Sell,Buy

Expected Result
Code:
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
# 2  
Old 02-10-2018
Please clarify: Do you want
  • database tables modified or inserted using
    • db internal tools (artisan[?] / SQL / ...)
    • external tools working on query results
  • output files to be operated upon
    • with which tools
    • with which results (store / print / transfer / ...)

Last edited by RudiC; 02-10-2018 at 03:13 PM..
# 3  
Old 02-20-2018
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 .
# 4  
Old 02-24-2018
Code:
$
$ 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.


$
$

Login to Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Convert rows into column along with header john_prince Shell Programming and Scripting 18 05-05-2017 06:00 AM
Convert Column data values to rows Hypesslearner Shell Programming and Scripting 1 03-05-2015 05:19 AM
Convert rows to column and add header redse171 Shell Programming and Scripting 4 10-10-2014 11:08 AM
Convert Rows into Column sai_2507 Shell Programming and Scripting 3 12-12-2013 05:19 PM
How to convert values in a line to rows? babom Shell Programming and Scripting 3 12-10-2013 09:38 AM
How to substract selective values in multi row, multi column file (using awk or sed?) nricardo Shell Programming and Scripting 4 10-15-2012 10:13 AM
Convert rows into column groups selvanraj Shell Programming and Scripting 0 07-22-2010 09:54 AM
Sort the multi column rows sandy1028 Shell Programming and Scripting 2 07-11-2010 12:06 PM
Convert Column to rows kaponeh Shell Programming and Scripting 5 03-31-2010 07:19 AM
convert rows into column cdfd123 Shell Programming and Scripting 3 01-11-2008 11:54 AM


All times are GMT -4. The time now is 02:25 AM.

Unix & Linux Forums Content Copyrightę1993-2018. All Rights Reserved.
UNIX.COM Login
Username:
Password:  
Show Password