Visit The New, Modern Unix Linux Community


DB2 Query -Convert multi values from column to rows


 
Thread Tools Search this Thread
Top Forums Programming DB2 Query -Convert multi values from column to rows
# 1  
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  
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 04:13 PM..
# 3  
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  
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.


$
$


Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #836
Difficulty: Medium
SOAP is a data serialization specification.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

DB2 Query modification to remove duplicate values using LISTAGG function

I am using DB2 v9 and trying to get country values in comma seperated format using below query SELECT distinct LISTAGG(COUNTRIES, ',') WITHIN GROUP(ORDER BY EMPLOYEE) FROM LOCATION ; Output Achieved MEXICO,UNITED STATES,INDIA,JAPAN,UNITED KINGDOM,MEXICO,UNITED STATES The table... (4 Replies)
Discussion started by: Perlbaby
4 Replies

2. Programming

DB2 Query to pick hierarchy values

Dear Team I am using DB2 v9 . I have a condition to check roles based on hierarchies like below example. 1.Ramesh has Roles as "Manager" and "Interviewer" 2.KITS has Roles as "Interviewer" 3.ANAND has Roles as "Manager" and "Interviewer" select * FROM TESTING NAME ... (6 Replies)
Discussion started by: Perlbaby
6 Replies

3. Shell Programming and Scripting

Convert Column data values to rows

Hi all , I have a file with the below content Header Section employee|employee name||Job description|Job code|Unitcode|Account|geography|C1|C2|C3|C4|C5|C6|C7|C8|C9|Csource|Oct|Nov|Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep Data section ... (1 Reply)
Discussion started by: Hypesslearner
1 Replies

4. Shell Programming and Scripting

Convert Rows into Column

Hi Experts, I have a requirement to convert rows into columns. For e.g. Input File: Output File should be like Appreciate if you could suggest code snippet(may be awk) for above requirement... Thanks in Advance for your help... (3 Replies)
Discussion started by: sai_2507
3 Replies

5. Shell Programming and Scripting

How to convert values in a line to rows?

hi, I am basically running a sql that returns me values and I have stored them to a variable for example value of the variable will be: 123 124 345 now I want to write values stored in the variable into a file as 123 124 345 thanks in advance (3 Replies)
Discussion started by: babom
3 Replies

6. Shell Programming and Scripting

How to substract selective values in multi row, multi column file (using awk or sed?)

Hi, I have a problem where I need to make this input: nameRow1a,text1a,text2a,floatValue1a,FloatValue2a,...,floatValue140a nameRow1b,text1b,text2b,floatValue1b,FloatValue2b,...,floatValue140b look like this output: nameRow1a,text1b,text2a,(floatValue1a - floatValue1b),(floatValue2a -... (4 Replies)
Discussion started by: nricardo
4 Replies

7. Shell Programming and Scripting

Sort the multi column rows

abc xyz - - - - - - - - - - - How to sort the second column in ascending order. (2 Replies)
Discussion started by: sandy1028
2 Replies

8. Shell Programming and Scripting

Convert Column to rows

Hi, I have a file with below contents. Heading1 Heading2 Heading3 Heading4 Value1 Value2 Value3 Value4 The file has only 2 rows and is tab separated The desired output is : Heading1 Value1 Heading2 Value2 Heading3 Value3 Heading4 Value4 CAn you please help? (5 Replies)
Discussion started by: kaponeh
5 Replies

9. Shell Programming and Scripting

convert rows into column

if u have a data 2 4 6 8 5 4 4 5 6 then result shud be like 2 4 6 7 5 4 4 5 6 (3 Replies)
Discussion started by: cdfd123
3 Replies

10. Shell Programming and Scripting

flags to suppress column output, # of rows selected in db2 sql in UNIX

Hello, I am new to db2 SQL in unix so bear with me while I try to explain the situation. I have a text file that has the contents of the where condition that I am using for a db2 SQL in UNIX ksh. Here is the snippet. if ; then echo "Begin processing VALUEs" ... (1 Reply)
Discussion started by: jerardfjay
1 Replies

Featured Tech Videos