DB2 Query -Convert multi values from column to rows Post: 303013634

Sponsored Content

Top Forums Programming DB2 Query -Convert multi values from column to rows Post 303013634 by durden_tyler on Saturday 24th of February 2018 09:04:24 PM

02-24-2018

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.


$
$

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

10 More Discussions You Might Find Interesting

1. 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" ...

2. 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. 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?

4. Shell Programming and Scripting

Sort the multi column rows

abc xyz - - - - - - - - - - - How to sort the second column in ascending order.

5. 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 -...

6. 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

7. 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...

8. 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 ...

9. 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 ...

10. 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...

LEARN ABOUT PHP

db2_statistics

DB2_STATISTICS(3)							 1							 DB2_STATISTICS(3)

db2_statistics - Returns a result set listing the index and statistics for a table

SYNOPSIS

       resource db2_statistics (resource  $connection, string  $qualifier, string  $schema, string  $table-name, bool  $unique)

DESCRIPTION

	Returns a result set listing the index and statistics for a table.

PARAMETERS

	      o $connection
		- A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

	      o $qualifier
		- A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

	      o $schema
		-  The	schema that contains the targeted table. If this parameter is NULL, the statistics and indexes are returned for the schema
		of the current user.

	      o $table_name
		- The name of the table.

	      o $unique
		- An integer value representing the type of index information to return.

		     o $0
		       - Return only the information for unique indexes on the table.

		     o $1
		       - Return the information for all indexes on the table.

RETURN VALUES

	Returns a statement resource with a result set containing rows describing the statistics and indexes for  the  base  tables  matching  the
       specified parameters. The rows are composed of the following columns:

       +--------------+---------------------------------------------------+
       | Column name  | 						  |
       |	      | 						  |
       |	      | 		   Description			  |
       |	      | 						  |
       +--------------+---------------------------------------------------+
       |  TABLE_CAT   | 						  |
       |	      | 						  |
       |	      | The catalog that contains the table. The value is |
       |	      | NULL if this table does not have catalogs.	  |
       |	      | 						  |
       | TABLE_SCHEM  | 						  |
       |	      | 						  |
       |	      |    Name of the schema that contains the table.	  |
       |	      | 						  |
       | TABLE_NAME   | 						  |
       |	      | 						  |
       |	      | 	       Name of the table.		  |
       |	      | 						  |
       | NON_UNIQUE   | 						  |
       |	      | 						  |
       |	      |  An integer value representing whether the  index |
       |	      | prohibits  unique values, or whether the row rep- |
       |	      | resents statistics on the table itself: 	  |
       |	      | 						  |
       |	      |        box, tab (|); c | c | .	T{ Return value   |
       |	      | 						  |
       |	      | 		 Parameter type 		  |
       |	      | 						  |
       +--------------+---------------------------------------------------+
       |0 (SQL_FALSE) | 						  |
       |	      | 						  |
       |	      |        The index allows duplicate values.	  |
       |	      | 						  |
       |1 (SQL_TRUE)  | 						  |
       |	      | 						  |
       |	      | 	The index values must be unique.	  |
       |	      | 						  |
       |	      | 						  |
       |    NULL      | 						  |
       |	      | 						  |
       |	      | This row is statistics information for the  table |
       |	      | itself. 					  |
       |	      | 						  |
       +--------------+---------------------------------------------------+
       T} T{ INDEX_QUALIFIER

       T} |T{ A string value representing the qualifier that would have to be prepended to INDEX_NAME to fully qualify the index.

       T} T{ INDEX_NAME

       T} |T{ A string representing the name of the index.

       T} T{ TYPE

       T} |T{
	An integer value representing the type of information contained in this row of the result set:

       +------------------------+---------------------------------------------------+
       |     Return value	|						    |
       |			|						    |
       |			|		   Parameter type		    |
       |			|						    |
       +------------------------+---------------------------------------------------+
       |  0 (SQL_TABLE_STAT)	|						    |
       |			|						    |
       |			| The  row  contains  statistics  about  the  table |
       |			| itself.					    |
       |			|						    |
       |1 (SQL_INDEX_CLUSTERED) |						    |
       |			|						    |
       |			| The row contains information	about  a  clustered |
       |			| index.					    |
       |			|						    |
       |  2 (SQL_INDEX_HASH)	|						    |
       |			|						    |
       |			| The  row  contains  information  about  a  hashed |
       |			| index.					    |
       |			|						    |
       |  3 (SQL_INDEX_OTHER)	|						    |
       |			|						    |
       |			| The row contains  information  about	a  type  of |
       |			| index that is neither clustered nor hashed.	    |
       |			|						    |
       +------------------------+---------------------------------------------------+
       T} T{ ORDINAL_POSITION

       T} |T{ The 1-indexed position of the column in the index.  NULL if the row contains statistics information about the table itself.

       T} T{ COLUMN_NAME

       T} |T{ The name of the column in the index.  NULL if the row contains statistics information about the table itself.

       T} T{ ASC_OR_DESC

       T} |T{

       A  if  the column is sorted in ascending order, D if the column is sorted in descending order, NULL if the row contains statistics informa-
       tion about the table itself.

       T} T{ CARDINALITY

       T} |T{
	If the row contains information about an index, this column contains an integer value representing the number  of  unique  values  in  the
       index.	If  the  row contains information about the table itself, this column contains an integer value representing the number of rows in
       the table.

       T} T{ PAGES

       T} |T{
	If the row contains information about an index, this column contains an integer value representing the number of pages used to	store  the
       index.  If the row contains information about the table itself, this column contains an integer value representing the number of pages used
       to store the table.

       T} T{ FILTER_CONDITION

       T} |T{ Always returns NULL.

       T}

SEE ALSO

       db2_column_privileges(3), db2_columns(3), db2_foreign_keys(3), db2_primary_keys(3), db2_procedure_columns(3),  db2_procedures(3),  db2_spe-
       cial_columns(3), db2_table_privileges(3), db2_tables(3).

PHP Documentation Group 													 DB2_STATISTICS(3)