I have a below requirement let me know the optimized query.
Create table test_123 ( sr varchar2(1000));
Insert into test_123 values ('1,2,3,4,5');
Insert into test_123 values ('6,7,9,10,11');
Insert into test_123 values ('9,2,3,8,5');
I need output as :
Sr_output
1
2
3
4
5
6
7
8
9
10
11
I need all the values in the column sr in a single column and mutiple rows.
CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
I have a table where i want the output to be a single column
ex: select from t1;*
query output_
rownum col_1
1 8217
2 6037
3 5368
4 5543
5 5232
i would like the result to be : *8217,6037,5368,5543,5232*
i did look on the web but can't find a solution that is easily understood.
I want to replace below multiple call to procedure with a Single query. Currently this proc is getting called multiple times from application.
FUNCTION f_get_shows_info(i_booking_wk_id IN NUMBER, i_screen_id IN NUMBER)
RETURN VARCHAR2 IS
v_act_shows booking_wk_screen.act_shows%TYPE;
v_expected_shows booking_wk_screen.expected_shows%TYPE;
v_return VARCHAR2(50);
BEGIN
SELECT NVL(act_shows, 1), NVL(expected_shows, 1)
INTO v_act_shows, v_expected_shows
FROM booking_wk_screen
WHERE booking_wk_id = i_booking_wk_id
[code]...
Is there anyway through which we can achieve this in Oracle 10g.
I need a single select query which converts all the rows into a single value . Below is my requirement :
Create table email_tbl(emailid varchar2(30));
insert into email_tbl('1@y.com');
insert into email_tbl('2@y.com');
insert into email_tbl('3@y.com');
insert into email_tbl('4@y.com');
Now , I need a single select query which gives me the below results.
1@y.com,2@y.com,3@y.com,4@y.com
I have done the above by using a cursors in the pl/sql objects.But want to achieve this with a single sql/query.
I want to convert a column value to a delimited string using a query.
Example
TableA
Col1 Col2 Col3
1 x200 MIS-X
2 x200 BTS-X
3 x200 TYR-X
4 x100 YRY-X
Select Col3 From TableA where Col2 = 'x200'
Expected Output:
'MIS-X','BTS-X','TYR-X'
I has a table of structure of varchar2 datatype.
NO
----------
1-2
3-4
5-6
desired output is:
SQL>1
2
3
4
5
6
The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.
I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.
How to update two table column in single query ?
example :
update table1 t1 ,table2 t2
set t1.column = 'Yes',t2.column='Yes'
where t1.emp_code =t2.emp_code ;
I have a Table as below
Attribute_namePrimary Seconday
DNS 204.109.167.1204.109.167.2
DNS 204.109.167.2204.109.167.3
NAT 138.20.37.136138.20.37.137
NAT 138.20.20.116138.20.20.117
For a Specific requirement
I need the select data output like below
[b]DNS NAT Type[/b]
204.109.167.1138.20.37.136Primary
204.109.167.2138.20.20.116Primary
204.109.167.2138.20.37.137Secondary
204.109.167.3138.20.20.117Secondary
I want to convert row data into column and I mentioned here,
This is my table format, A B C------------------col1 1 101col2 2 102col3 3 103
The above query need to like this, COL1 COL2 COL3----------------------------------1 2 3101 102 103
how to convert rows data into column...
View 7 Replies View RelatedCREATE TABLE EMP(NAME VARCHAR2(10 BYTE))
INSERT INTO EMP VALUES ('C');
INSERT INTO EMP VALUES ('A');
INSERT INTO EMP VALUES ('T');
SELECT xmlelement("NAME",NAME) FROM EMP;
I am trying to convert column data to xml format, but I get this error message:
Quote:The query fails because all columns types are currently not supported. I am using:
Quote:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
SQLTools 1.5.0 Beta build 9 as EDITOR
How can i convert data in NCLOB column to BLOB variable? NCLOB now is tored in UTF16. The data character set is ISO8859P2. Need to keep BLOB at unicode.
View 11 Replies View RelatedWe have to update a single column data in about 10 tables which has child/parent table relations, pk/fk constraints.. The column that we are updating is a part of primary key in half of the tables and part of foreign key in the other half tables.. I'm thinking of disabling all the foreign key constraints in the tables then update the column data then enable the foreign key constraints in these tables.
View 7 Replies View Relatedcan we delete multiples table through the single query?
suppose we have 2 table first one is emp and second is client
i want delete all data from emp and client through the single line query
I want to load single column data into multiple columns.
View 3 Replies View RelatedHow can I write a query to combine amount1 and amount1 into single column amount?
create table a (
id_code varchar(20),
invoice_date date,
invoice_type number,
interest number,
advance number,
invoice number
);
[code]....
How do I convert multiple rows into single row data. For example.....
I am looking for oracle query for below table output that would have a sql query output as query output below.
I was trying with connect by but was unable to prepare query.
Table output_
---------------------------
ID TYPE VALUE
---------------------------
1012 1 2
1012 6 77
1015 1 1
1015 6 78
1018 1 2
1018 6 79
Query output_
----------------------------
ID VALUE1 VALUE2
----------------------------
1012 2 77
1015 1 78
1018 2 79
CREATE TABLE T1 ( id NUMBER,
START_date DATE,
end_date DATE,
end_date1 DATE,
end_date2 DATE,
end_date3 DATE,
LEVEL1 number
)
/
[Code]...
I have data in the first table as mentioned above I need to insert multiple rows into the second table for the same ID depends on the level, If it is level 1 then two rows for same ID first reocrd start_date as the start_date and end_date as end_date from the table t1 for second record start_date is end_date in t1 and end_date for this record is end_date1 column in table t1.
If the level is 3 then the table t2 should have four records for one id and the phase is the value for each record for one ID for example in level 3 we have 4 records for one id and phase should be 1,2,3,4.
how to convert a single 'ragged' table like:
MAIN_KEY SUB_KEY DATA
ABC 1 A
ABC 2 B
ABC 3 C
DEF 1 X
where:
MAIN_KEY and DATA are indeterminate, and
SUB_KEY = 1 .. n [always starts with 1 for a given MAIN_KEYD]
into:
MAIN_KEY SUB_KEY DATA
ABC 1 A
ABC 2 B
ABC 3 C
ABC 4 {null}
ABC 5 {null}
DEF 1 X
DEF 2 {null}
DEF 3 {null}
DEF 4 {null}
DEF 5 {null}
in the case where n = 5?
I have a single node Oracle E-Biz 12.1.3 Installation.We plan to convert this to a dual node RAC install and I wanted to run by the steps to perform this AND clone over the current production system to the new RAC system (note this will not be using ASM - but will be based on NetApp NFS)
1. Install GRID 11.2.0.3
- installed acorss two nodes, Binaries can be shared
- voting disks / cluster rigistry MUST be shared
2. Install Database Software for RAC
- Binaries cannot be shared and must be installed on each node independently
3. Configure the GRID for database (database / listeners)
4. Clone over the stand alone system (clone database as RAC)
I will be using SCAN - and therefore would expect all the web services / concurrent manager nodes to point to the SCAN hostname as opposed to individual host names.
Inspiration of a java program that converts a oracle database to one single XML document?
View 1 Replies View RelatedI have extracted in the following XML document some Mpeg7 visual descriptors from an image and I saved it in an XMLType column. I would like to use XMLQuery to extract the data from the Value node. how write such a query. I could not get the proper Xpath to any node.
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Mpeg7 xmlns="http://www.mpeg7.org/2001/MPEG-7_Schema" xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance">
<DescriptionUnit xsi:type="DescriptorCollectionType">
[code]...
I have 11gR2 GI installed on two nodes. I am trying to convert a 10g single instance (uses ASM) database to RAC and getting this error. I am trying using 12c as well as manually by using rconfig.
[main] [21:19:15:145] [ASMInstance.initialize:135] First record =[Ljava.lang.String;@958bb8
java.lang.NullPointerException
at oracle.sysman.assistants.rconfig.engine.ASMInstance.initialize(ASMInstance.java:153)
at oracle.sysman.assistants.rconfig.engine.Step.execute(Step.java:283)
[code]...
how to use dbms_xmlgen to take the results of a query and convert it into a XML formatted data. But if a column (clob) contains data in an XML format, is there a way to query that column and easily parse the data? I know that I could write loops that do string searches for each of the XML tags and nested tags.
View 9 Replies View Relatedhow can i change row as column in oracle.
I wrote the one query which is return the following output..
Query:
SELECT RMC_N_ID,NVL(DISCOUNT_N_ID,NULL) AS DISCOUNT_N_ID,NVL(NULL,'') AS LOADINGS_N_ID,NVL(NULL,'') AS WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_DISCOUNT WHERE RMC_N_ID =224 AND DISCOUNT_N_ID IS NOT NULL
UNION
SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(LOADING_N_ID,NULL) AS LOADING_N_ID ,NVL(NULL,'') AS WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_LOADING WHERE RMC_N_ID =224
UNION
SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(NULL,NULL) AS LOADING_N_ID ,WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_WARCOMB WHERE RMC_N_ID =224
UNION
SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(NULL,NULL) AS LOADING_N_ID ,NVL(NULL,'') AS WARCOMB_N_ID, ENDORSECOMB_N_ID FROM UWRMC_ENDORSECOMB WHERE RMC_N_ID =224
Result:
RMC_N_ID DISCOUNT_N_ID LOADINGS_N_ID WARCOMB_N_ID ENDORSECOMB_N_ID
---------- ------------- ------------- ------------ ----------------
224 0 87 0 0
224 0 88 0 0
224 0 0 0 93
224 0 0 0 94
224 0 0 88 0
224 0 0 89 0
224 82 0 0 0
224 83 0 0 0
I want the result like below
RMC_N_ID DISCOUNT_N_ID LOADINGS_N_ID WARCOMB_N_ID ENDORSECOMB_N_ID
---------- ------------- ------------- ------------ ----------------
224 82 87 88 93
224 83 88 89 94
How can i get this..
my data looks like:-
Ename Job salary
----- ----- -------
A manager 10000
B clerk 60000
C assistant 30000
A manager 50000
B clerk 20000
D accountant 40000
I want the result while select all data should look like:-
A B C D
manager clerk assistant accountant
10000 60000 30000 40000
50000 20000
how to convert row into column
below are my row data from qury
empno ename sal first_name last_name dof
10 rahul 1000 rah rao 01-jan-2000
20 rohit 2000 roh subuu 02-jan-2000
require output
10 20
rahul rohit
1000 2000
rahrao roh
rao subuu
01-jan-2000 02-jan-2000
I have table having below records.
empno ename deptno
101 a 10
102 b 20
103 c 10
104 d 20
105 e 30
Normal Output using Group by.
deptno count(*)
----------------------
10 2
20 2
30 1
I want to display like below(rows into columns)
--------------------------------------------------------
Requiredl Output
----------------------
10 20 30
2 2 1
Deptnos are in first row and cout of deptno nos are second row.
How to convert station & date wise multiple rows to date wise column. I have a table TEST like this,
STN_Name Date State
DELHI1-Sep-201G
DELHI2-Sep-201B
DELHI3-Sep-201G
DELHI4-Sep-201G
DELHI5-Sep-201G
DELHI6-Sep-201M
DELHI7-Sep-201G
DELHI8-Sep-201G
DELHI9-Sep-201G
DELHI ......... ..
DELHI 30 Sep 2012 G
KOLKATA1-Sep-201G
KOLKATA2-Sep-201B
KOLKATA3-Sep-201B
KOLKATA4-Sep-201B
KOLKATA5-Sep-201G
KOLKATA6-Sep-201M
KOLKATA7-Sep-201G
KOLKATA8-Sep-201G
KOLKATA9-Sep-201B
I want output like this
STN_Name1-Sep-20122-Sep-20123-Sep-20124-Sep-20125-Sep-2012............Sep 2012
DELHI G B G G G M
KOLKATA G B B B G M