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_columns
DB2_COLUMNS(3) 1 DB2_COLUMNS(3)db2_columns - Returns a result set listing the columns and associated metadata for a tableSYNOPSIS
resource db2_columns (resource $connection, [string $qualifier], [string $schema], [string $table-name], [string $column-name])
DESCRIPTION
Returns a result set listing the columns and associated metadata 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. To match all schemas, pass '%'.
o $table-name
- The name of the table or view. To match all tables in the database, pass NULL or an empty string.
o $column-name
- The name of the column. To match all columns in the table, pass NULL or an empty string.
RETURN VALUES
Returns a statement resource with a result set containing rows describing the columns matching the specified parameters. The rows are com-
posed of the following columns:
+------------------+---------------------------------------------------+
| Column name | |
| | |
| | Description |
| | |
+------------------+---------------------------------------------------+
| TABLE_CAT | |
| | |
| | Name of the catalog. The value is NULL if this |
| | table does not have catalogs. |
| | |
| TABLE_SCHEM | |
| | |
| | Name of the schema. |
| | |
| TABLE_NAME | |
| | |
| | Name of the table or view. |
| | |
| COLUMN_NAME | |
| | |
| | Name of the column. |
| | |
| DATA_TYPE | |
| | |
| | The SQL data type for the column represented as |
| | an integer value. |
| | |
| TYPE_NAME | |
| | |
| | A string representing the data type for the col- |
| | umn. |
| | |
| COLUMN_SIZE | |
| | |
| | An integer value representing the size of the |
| | column. |
| | |
| BUFFER_LENGTH | |
| | |
| | Maximum number of bytes necessary to store data |
| | from this column. |
| | |
| DECIMAL_DIGITS | |
| | |
| | The scale of the column, or NULL where scale is |
| | not applicable. |
| | |
| NUM_PREC_RADIX | |
| | |
| | An integer value of either 10 (representing an |
| | exact numeric data type), 2 (representing an |
| | approximate numeric data type), or NULL (repre- |
| | senting a data type for which radix is not appli- |
| | cable). |
| | |
| NULLABLE | |
| | |
| | An integer value representing whether the column |
| | is nullable or not. |
| | |
| REMARKS | |
| | |
| | Description of the column. |
| | |
| COLUMN_DEF | |
| | |
| | Default value for the column. |
| | |
| SQL_DATA_TYPE | |
| | |
| | An integer value representing the size of the |
| | column. |
| | |
|SQL_DATETIME_SUB | |
| | |
| | Returns an integer value representing a datetime |
| | subtype code, or NULL for SQL data types to which |
| | this does not apply. |
| | |
|CHAR_OCTET_LENGTH | |
| | |
| | Maximum length in octets for a character data |
| | type column, which matches COLUMN_SIZE for sin- |
| | gle-byte character set data, or NULL for non- |
| | character data types. |
| | |
|ORDINAL_POSITION | |
| | |
| | The 1-indexed position of the column in the ta- |
| | ble. |
| | |
| IS_NULLABLE | |
| | |
| | A string value where 'YES' means that the column |
| | is nullable and 'NO' means that the column is not |
| | nullable. |
| | |
+------------------+---------------------------------------------------+
SEE ALSO db2_column_privileges(3), db2_foreign_keys(3), db2_primary_keys(3), db2_procedure_columns(3), db2_procedures(3), db2_special_columns(3),
db2_statistics(3), db2_table_privileges(3), db2_tables(3).
PHP Documentation Group DB2_COLUMNS(3)