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 :
Expected Result
Also column values for above will be dynamic and not restricted to just 2 or 3 values and are always comma(,) separated.
Please guide
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 PHP
db2_special_columns
DB2_SPECIAL_COLUMNS(3) 1 DB2_SPECIAL_COLUMNS(3)db2_special_columns - Returns a result set listing the unique row identifier columns for a tableSYNOPSIS
resource db2_special_columns (resource $connection, string $qualifier, string $schema, string $table_name, int $scope)
DESCRIPTION
Returns a result set listing the unique row identifier columns 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 which contains the tables.
o $table_name
- The name of the table.
o $scope
- Integer value representing the minimum duration for which the unique row identifier is valid. This can be one of the following
values:
+--------------+--------------------------------------+---+
|Integer value | | |
| | | |
| | SQL constant | |
| | | |
| | Description | |
| | | |
+--------------+--------------------------------------+---+
| 0 | | |
| | | |
| | SQL_SCOPE_CURROW | |
| | | |
| | Row identifier is valid only while | |
| | the cursor is positioned on the row. | |
| | | |
| 1 | | |
| | | |
| | SQL_SCOPE_TRANSACTION | |
| | | |
| | Row identifier is valid for the | |
| | duration of the transaction. | |
| | | |
| 2 | | |
| | | |
| | SQL_SCOPE_SESSION | |
| | | |
| | Row identifier is valid for the | |
| | duration of the connection. | |
| | | |
+--------------+--------------------------------------+---+
RETURN VALUES
Returns a statement resource with a result set containing rows with unique row identifier information for a table. The rows are composed
of the following columns:
+------------+---------------------------------------------------+
|Column name | |
| | |
| | Description |
| | |
+------------+---------------------------------------------------+
| SCOPE | |
| | |
| | |
| | |
| | box, tab (|); c | c | c | . T{ Integer |
| | value |
| | |
| | SQL constant |
| | |
| | Description |
| | |
+------------+---------------------------------------------------+
| 0 | |
| | |
| | SQL_SCOPE_CURROW |
| | |
| | Row identifier is valid only while the cursor is |
| | positioned on the row. |
| | |
| 1 | |
| | |
| | SQL_SCOPE_TRANSACTION |
| | |
| | Row identifier is valid for the duration of the |
| | transaction. |
| | |
| 2 | |
| | |
| | SQL_SCOPE_SESSION |
| | |
| | Row identifier is valid for the duration of the |
| | connection. |
| | |
+------------+---------------------------------------------------+
T} T{ COLUMN_NAME
T} |T{ Name of the unique column.
T} T{ DATA_TYPE
T} |T{ SQL data type for the column.
T} T{ TYPE_NAME
T} |T{ Character string representation of the SQL data type for the column.
T} T{ COLUMN_SIZE
T} |T{ An integer value representing the size of the column.
T} T{ BUFFER_LENGTH
T} |T{
Maximum number of bytes necessary to store data from this column.
T} T{ DECIMAL_DIGITS
T} |T{
The scale of the column, or NULL where scale is not applicable.
T} T{ NUM_PREC_RADIX
T} |T{
An integer value of either 10 (representing an exact numeric data type), 2 (representing an approximate numeric data type), or NULL (rep-
resenting a data type for which radix is not applicable).
T} T{ PSEUDO_COLUMN
T} |T{ Always returns 1.
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_sta-
tistics(3), db2_table_privileges(3), db2_tables(3).
PHP Documentation Group DB2_SPECIAL_COLUMNS(3)