PL/SQL :: Multiple Row Convert Into Single Row
Jan 4, 2013
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
View 5 Replies
ADVERTISEMENT
Feb 28, 2012
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.
View 3 Replies
View Related
Jul 19, 2013
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
View 4 Replies
View Related
Oct 17, 2012
How to merge multiple rows into single row (but multiple columns) efficiently.
For example
IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100
where different attributes mean different thing for each Information_type. For example for Information_Type=Location
Attribute_1 means Country
Attribute_2 means State and so on.
For example for Information_Type=Stats
Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.
I want to create a view that shows like below:
IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100
View 1 Replies
View Related
Jul 26, 2012
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.
View 16 Replies
View Related
Dec 11, 2011
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?
View 6 Replies
View Related
Dec 11, 2012
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.
View 9 Replies
View Related
Oct 11, 2012
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.
View 2 Replies
View Related
Jan 16, 2013
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.
View 1 Replies
View Related
Dec 14, 2010
Inspiration of a java program that converts a oracle database to one single XML document?
View 1 Replies
View Related
Oct 5, 2012
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]...
View 1 Replies
View Related
Aug 30, 2004
I have a table called N1
N1_no Srvarea_type_cd
1 P
1 P
2 C
2 C
2 C
3 I
Another table N2
N1_no srvarea_txt
1 ABCD
2 DEFG
3 XYZA
Can i get a query so that the data can be displayed in the following way ..
P C I
ABCD DEFG XYZA
View 3 Replies
View Related
Aug 31, 2010
i have a column 'name' in which value is 'Shailesh Negi',i have to insert 'shailesh' into'first name' column and 'Negi' into 'last name' column respectively.
View 20 Replies
View Related
Sep 18, 2013
I have a table as col_1 number,col_2 varchar2(10)
col_1 col_2
1 abcdefghijkl
what i want is i've to split
col_1 col_2
1 abcdefghij
1 kl
View 4 Replies
View Related
Mar 24, 2010
I have records:
owner company
A X
A Y
A Z
B X
B Y
C X
owner companyX companyY companyZ
A 1 1 1
B 1 1 0
C 1 0 0
How do I write the SQL?
View 2 Replies
View Related
Jul 8, 2013
I want to insert multiple records in a single row. Example: I have a below query
select '"'||c1||'","'||c2||'","'||c3||'"'from (select 'ABC' as C1,'ZYX' as C2,'TEST' AS c3from dual unionselect 'A1' as C1,'a2' as C2,'A3' AS c3from dual)
And I want to insert the above 2 records in table T1 as a single row as below:
"A1","a2","A3""ABC","ZYX","TEST" Column1"A1","a2","A3""ABC","ZYX","TEST"
View 6 Replies
View Related
Jun 7, 2011
I am facing some problem, while fetching the result that I want to. I have a table with name "test", there are two columns:
"id" type int
"text_data" type varchar2(2000)
Sample Data:
ID TEXT_DATA
------- ------------
10 Hi Deepak, My designation id is dsha21. Thanks Rohit
Now I tried to replace the value for "Deepak","dsha21" and "Rohit" using nested replace function and I succeded but that was for static. Now while creating SQL procedure where I am going to make the values of "Deepak","dsha21" and "Rohit" some static variables. I want to pass the values to be replaced with static parameter.
If I give you simple example of my requirement that would be example of a sms send to all customers by a telephone company. Content is same only the Name of customer is replaced everytime.
View 10 Replies
View Related
Apr 10, 2012
how does this query execute? what kind of a query is this called?
mysql> select ename,(select dname from dept where deptno=e.deptno ) as dname -> from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
View 8 Replies
View Related
Mar 22, 2013
writing the sql, to transform a single row into multiple rows. I am trying to create multiple rows based on a value of a column in the table.In the below example, I am trying to create the rows based on the 'Col2' values. find the below example:
Original table data:
Col1 Col2 Col3 Col4
Row1 a1 a,b,c 01 ON
Row2 b1 d,e,f 02 OFF
Row3 c1 g,h 03 ON
I want the above table to be transformed into below:
Col1 Col2 Col3 Col4
Row1 a1 a 01 ON
Row1 a1 b 01 ON
Row1 a1 C 01 ON
Row2 b1 d 02 OFF
Row2 b1 e 02 OFF
Row2 b1 f 02 OFF
Row3 c1 g 03 ON
Row3 c1 h 03 ON
View 2 Replies
View Related
Jan 27, 2012
I have a scenario where the requirement is to create a "SEQ NO". Based on the 'Tasks' & it's creation date. Where there are multiple 'tasks' for a single SR.
The following example will make it clear
SR TASK Created on SEQ NO
11009 2345 14/10/1988 12:15:17 1
11009 2346 14/10/1988 12:15:57 2
11234 - - -
If there is a SR which has no task then the SEQ NO should be 0. And the seq no should be in the order of the created on field & if 2 tasks are created at the same time i.e suppose 2345 & 2346 have the same time stamp then the SEQ no should be 1,2 respectively n not both as 1.
View 5 Replies
View Related
Apr 9, 2013
need to create a table with single column by using select statement with multiple columns
For Ex- i have 1 row with 10 columns (may be more than 10) like
'A','B','C','D','E','F','G','H',I','J'
i written sql like
select 'A','B','C','D','E','F','G','H','I','J' from dual
result is - 'A','B','C','D','E','F','G','H','I','J' with 10 columns
Now i need output lik this using SQL
Text
------
'A'
'B'
'C'
'D'
[code]...
sort out this problem.
View 6 Replies
View Related
Oct 17, 2012
I want to run multiple IF Else statements in a single select SQL, each statement is one SQL operating on the same table, what is the best way to write this select SQL query ? If it is PL/SQL, when i get the result from the first IF statement I will skip the remaining execution, and so on..
View 9 Replies
View Related
Sep 6, 2012
I have a sql query as below :
select order_number,
(select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null)
from hz_contact_points,
hz_parties hz
WHERE hz.party_id=hcp.owner_table_id) Email
FROM oe_order_headers_all h
WHERE h.order_number='102'
....................
..............
Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.
In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values
View 3 Replies
View Related
Jun 25, 2012
I am trying to write a script where a particular post code from a table is having more than 3 telephone numbers.Both the columns are in the same table. How to fetch.
Table is P_Order
Columns are DELIVERY_POSTCODE and TEL_NO...
Condition DELIVERY_POSTCODE has more than 3 TEL_NO
View 1 Replies
View Related
Sep 26, 2010
I have created tab canvases in the tab canvas there is three tab pages.and i have been used one datablock(use the datablock wizard) all the table column(items) is displayed only in the single tab pages--frames.for example
datablock--items_1.
tabcanvases--tabpages_1--frames_1--display all datablock item.
--tabpages_2--frames_2--none to display.
--tabpages_3--frames_3--none to display.
so i want to display datablock in all the three frames.
View 3 Replies
View Related
Jul 21, 2011
My current system have 1 submit button with single form. This submit button will call *file_content.upload*.
htp.p('function on_submit() {');
htp.p('...the rest of my code here..');
htp.p('document.forms[0].submit();');
htp.p('return true;');
htp.p('}');
htp.p('<form enctype="multipart/form-data" method="post" action="file_content.upload">');
htp.p('...the rest of my code here..');
htp.p('<input type="button" id="btn_Submit" value="Submit" onclick="on_submit()"></form>');
My question is, i want to enhance this by adding additional 1 submit button with value "Save". But this new "Save" button will call/trigger different file. This new button will call *file_content.update*. How to accomplish this and how to differentiate these 2 button?
htp.p('<input type="button" id="btn_Submit" value="Submit" onclick="on_submit()">
<input type="button" id="btn_Save" value="Save"></form>');
View 12 Replies
View Related
Sep 10, 2012
CREATE TABLE prim_tbl
(id NUMBER,--- id is not primary key here
description VARCHAR2(30));
INSERT ALL
INTO prim_tbl VALUES (1,'aad')
INTO prim_tbl VALUES (1,'aads')
INTO prim_tbl VALUES (2,'bb')
INTO prim_tbl VALUES (2,'cc')
INTO prim_tbl VALUES (2,'dd')
SELECT * FROM dual;
I want to select the ids only one time, i.e my output will have only two rows: one row with id as 1 and other row with id 2 whatever be the description.
desired output sample:
Quote:1, aad
2, bb
I used:
select distinct(id),description from prim_tbl;
but it did not give the required result.How can I get it??
View 6 Replies
View Related
Oct 5, 2011
I have following tables with data as under:
table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9
I need to generate output as :
column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null
Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?
View 5 Replies
View Related
Jan 27, 2012
Actully i am updating two table of data.. but below error message came..
update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno
and empno = 7788)
set ename = 'X', dname = 'Y'
/
Error at line 1
ORA-01776: cannot modify more than one base table through a join view
View 10 Replies
View Related
Jul 24, 2009
Updating multiple ROWS with different values using single statement. Requirement is to update one column in a table with the values in the other table.
Say we have 3 tables, CORPORATION,CORPORATE PROFILE and MEMBER.
Each MEMBER has CORPORATE PROFILE which in turn is associated with CORPORATION. Now I need to update MEMBER table with CORPORATION identifier for members who belong to corporations with identifiers say 'ABC' and 'DEF'.
MEMBER table contains column 'CORPIDENTIFIER '. CORPORATEPROFILE table contains MEMBERID and CORPORATIONID,this will associate a member with the corporation. CORPORATION table contains ID and CORPIDENTIFIER.
Using the below query I am getting error,ORA-01427:single-row subquery returns more than one row
UPDATE MEMBER M SET M.CORPIDENTIFIER=
(SELECT A.IDENTIFIER FROM CORPORATION A,CORPORATEPROFILE B
WHERE B.CORPORATIONID=A.ID AND B.MEMBERID=M.ID AND (A.IDENTIFIER LIKE 'ABC' OR A.IDENTIFIER LIKE 'DEF'))
Sub query in the above query returns multiple rows and hence it is throwing the error.More than one members are associated with Corporations ABC and DEF. Is there any way possible to update all the rows in single query with out iterating the result set of sub query.
View 1 Replies
View Related
