Programming

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

DB2 Query -Convert multi values from column to rows

👤 Login to reply

    #1  
Old 02-10-2018
Perlbaby Perlbaby is offline
Registered User
 
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
Sponsored Links
    #2  
Old 02-10-2018
RudiC RudiC is offline Forum Staff  
Moderator
 
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..
Sponsored Links
    #3  
Old 02-20-2018
Perlbaby Perlbaby is offline
Registered User
 
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
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
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.


$
$

Sponsored Links
👤 Login to reply

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 Column data values to rows Hypesslearner Shell Programming and Scripting 1 03-05-2015 05:19 AM
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
Sort the multi column rows sandy1028 Shell Programming and Scripting 2 07-11-2010 12:06 PM
flags to suppress column output, # of rows selected in db2 sql in UNIX jerardfjay Shell Programming and Scripting 1 11-02-2005 05:48 AM



All times are GMT -4. The time now is 01:40 PM.

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





Not a Forum Member?
Forgot Password?