$
$ 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.
$
$
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)
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)
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)
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)
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)
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)
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)
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
LEARN ABOUT CENTOS
tcftest
TCFTEST(1) Tokyo Cabinet TCFTEST(1)NAME
tcftest - test cases of the fixed-length database API
DESCRIPTION
The command `tcftest' is a utility for facility test and performance test. This command is used in the following format. `path' specifies
the path of a database file. `rnum' specifies the number of iterations. `width' specifies the width of the value of each record. `lim-
siz' specifies the limit size of the database file.
tcftest write [-mt] [-nl|-nb] [-rnd] path rnum [width [limsiz]]
Store records with keys of 8 bytes. They change as `00000001', `00000002'...
tcftest read [-mt] [-nl|-nb] [-wb] [-rnd] path
Retrieve all records of the database above.
tcftest remove [-mt] [-nl|-nb] [-rnd] path
Remove all records of the database above.
tcftest rcat [-mt] [-nl|-nb] [-pn num] [-dai|-dad|-rl] path rnum [limsiz]]
Store records with partway duplicated keys using concatenate mode.
tcftest misc [-mt] [-nl|-nb] path rnum
Perform miscellaneous test of various operations.
tcftest wicked [-mt] [-nl|-nb] path rnum
Perform updating operations selected at random.
Options feature the following.
-mt : call the function `tcfdbsetmutex'.
-nl : enable the option `FDBNOLCK'.
-nb : enable the option `FDBLCKNB'.
-rnd : select keys at random.
-wb : use the function `tcfdbget4' instead of `tcfdbget2'.
-pn num : specify the number of patterns.
-dai : use the function `tcfdbaddint' instead of `tcfdbputcat'.
-dad : use the function `tcfdbadddouble' instead of `tcfdbputcat'.
-rl : set the length of values at random.
-ru : perform random operation on random key.
This command returns 0 on success, another on failure.
SEE ALSO tcfmttest(1), tcfmgr(1), tcfdb(3), tokyocabinet(3)Man Page 2012-08-18 TCFTEST(1)