Fetch all columns into one column oracle
How do i achieve to fetch all columns into one column.
I have below sample query
WITH da AS( SELECT 'a' col1, 'b' col2, 'c' col3 FROM dual UNION ALL SELECT '1' col1, '2' col1, '3' col1 FROM dual UNION ALL SELECT 'x' col1, 'y' col1, 'z' col1 FROM dual ) SELECT * FROM da; //RESULTS COL1|COL2|COL3| ----|----|----| a |b |c | 1 |2 |3 | x |y |z |
But what i want is the result to be as below with
| as the delimiter.
ONE_COL| -------| a|b|c | 1|2|3 | x|y|z |
My biggest problem is i don't know the column names, therefore i cant do as:
SELECT col1||'|'||col2||'|'||col3 AS ONE_COL FROM da;
how can i do this.
The following is just to demonstrate a way to achieve what you asked for. (Assuming I didn't misunderstand what you asked for.)
First some preparation.
- Create the
DAtable (according to the sample data in your question).
create table DA (COL1 char(1), COL2 char(1), COL3 char(1));
- Add rows to the table (again, according to the sample data in your question).
insert into DA values ('a','b','c'); insert into DA values ('1','2','3'); insert into DA values ('x','y','z');
- I understand that you want to insert the data from table
DAinto another table, so I created a second database table according to what I understood from your question.
create table RESULT (ONE_COL char(5));
- PL/SQL code that retrieves the names of the columns (assuming you know the table name) from the data dictionary, then creates a dynamic cursor to retrieve the values from that table (again in the format described in your question) and inserts the rows fetched by the dynamic cursor into a second database table (since this is your requirement as I understood it from your question).
declare L_COL varchar2(128); L_CUR sys_refcursor; L_ONE char(5); L_SQL varchar2(2000); -- cursor C_COLS is select COLUMN_NAME from USER_TAB_COLUMNS where TABLE_NAME = 'DA'; begin L_SQL := 'select '; open C_COLS; fetch C_COLS into L_COL; L_SQL := L_SQL || L_COL; while C_COLS%found loop fetch C_COLS into L_COL; if C_COLS%found then L_SQL := L_SQL || '||'; L_SQL := L_SQL || '''|''||'; L_SQL := L_SQL || L_COL; end if; end loop; close C_COLS; L_SQL := L_SQL || ' from DA'; open L_CUR for L_SQL; loop fetch L_CUR into L_ONE; exit when L_CUR%notfound; insert into RESULT values (L_ONE); end loop; close L_CUR; end;
- Finally, after running the above PL/SQL code...
select * from RESULT;
ONE_C ----- abc 123 xyz
Concatenate multiple results into one row - Ask Tom, What I am looking for is a way to concatenate all the Condition fields which have the same NCT_ID into one row. So that the output will look How to get this without ordering the concatenated column. January 11, 2017 - 3:28� The FETCH statement retrieves rows of data from the result set of a multiple-row query. You can fetch rows one at a time, several at a time, or all at once. The data is stored in variables or fields that correspond to the columns selected by the query. Syntax. fetch_statement::= Description of the illustration fetch_statement.gif. into_clause::=
Concat the dual columns within the with clause, that way you don't need know what the da columns are called.
WITH da AS( SELECT 'a' || '|' || 'b' || '|' || 'c' AS one_col FROM dual UNION ALL SELECT '1' || '|' || '2' || '|' || '3' AS one_col FROM dual UNION ALL SELECT 'x' || '|' || 'y' || '|' || 'z' AS one_col FROM dual ) SELECT * FROM da;
Oracle SELECT Select: Query Data From One or More Columns of a , To retrieve data from one or more columns of a table, you use the SELECT statement with the following syntax: SELECT column_1, column_2, SELECT name FROM customers; SELECT customer_id, name, credit_limit FROM customers; SELECT customer_id, name, address, website, credit_limit FROM customers; SELECT * FROM customers Re: Merge multiple columns into single column? SELECT rtrim (extract (xmltype (REPLACE (column_value,'</'',',</')),'/ROW/*/text ()'),',') col FROM TABLE (SELECT xmlsequence (dbms_xmlgen.getxmltype ('select * from employees').extract ('ROWSET/ROW')) FROM dual ); 22.
The database knows the column names. So use the dbms_sql package and parse your sql.
There are many solutions to extract the data afterwards so I don't get your point why you think that you don't know any column names
Oracle SQL: displaying multiple columns per row, Oracle SQL: displaying multiple column values per row. Oracle Database Tips This allows multiple table column values to be displayed in a single column, using the listagg built-in function : select deptno, FETCH c_dummy INTO lc_colval; FROM table_name; In this SELECT statement: First, specify the table name from which you want to query the data. Second, indicate the columns from which you want to return the data. If you have more than one column, you need to separate each by a comma (,).
Can I combine the results from multiple columns into a single , It is unclear to me what is a "more elegant way". Oracle you can use the following statement to make columns to rows select all_name from foo unpivot (all_name� Previous: Write a program in PL/SQL to FETCH more than one record and single column from a table. Next: Write a program in PL/SQL to FETCH multiple records and more than one columns from different tables.
List all columns in specific table in Oracle database, for FLOAT - binary digits;. data_scale - digits to right of decimal point in a number; nullable - flag indicating if column allows nulls values. Rows. One� 11g within group SQL. In Oracle 11g, we have the within group SQL clause to pivot multiple rows onto a single row. We also a have direct SQL mechanism for non first-normal form SQL display. This allows multiple table column values to be displayed in a single column, using the listagg built-in function : select.
The SELECTINTOstatement retrieves data from one or more database tables, and assigns the selected values to variables or collections. For a full description of the SELECTstatement, see Oracle Database SQL Reference. In its default usage (SELECT INTO), this statement retrieves one or more columns from a single row.
- You don't know the names of the columns that you're selecting?
- @JoanLaraGanau yes i dont know the column names .
- Do you want something you can run interactively in a client like SQL Developer? Or do you need a procedural solution which is called by other programs?
- @APC Any solution is welcome. we forgot pass to the DB but we can pass query to the db to return result whereby the result is inserted in another db/table.
- Well you can generate some dynamic SQL from the
INFORMATION_SCHEMAbut the whole situation seems odd. What is the purpose of selecting data from a table you don't know the columns for? How can it be useful if you don't know what it is? Maybe look at SSIS or something?
- i don't know the column names
- What table do you not know the columns of, 'da' or 'dual'? I made the assumption that in your real use case 'dual' is an actual table and not dual.
- I used that as a sample data. i send the query blindly and data is returned while inserted in another table. therefore i dont know the column names.
- if i have to know the column names. then i will have to first write a query that sends to the db and returns for me the column_names as one column. because i am not using an editor. The tool i am using i have to specify a column to insert the result, it is not a sql editor. i.e. i send query it returns while inserting the result in another table
- The returned query contains the column names. So the problem you have is that with tool x you don't know how to reverse engeneer a query?
- tool x has that limitation. since it is shared
- You have to provide more information about your system setup in your question. Otherwise it's not possible to provide a good solution.