PL/SQL :: Multiple Rows To Multiple Column
May 6, 2013
I have a table TableA containing 2 columns ( Name and Value). Here I know what are the values for column Name
TABLEA
=======
Name Parameter
-------------------------
Nexus 11
GPlay 21
Demo 31
I need a query which provides the below output
Desired Output:
======
First Second Third
11 21 31
I have tried the below query
SELECT
DECODE (name,'Nexus', parameter) First,
DECODE (name, 'GPlay', parameter) Second,
DECODE (name, 'Demo', parameter) Third
FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));
This gives me the output
First Second Third
11 <Empty> <empty>
<empty> 21 <empty?>
<empty?> <empty?> 31
Is there any way to get the output in single line.
View 3 Replies
ADVERTISEMENT
Mar 24, 2013
I have one table , with one column having 2,3 or 4 machine codes , i need to display them as each row per machine code will it be possible to do as i have thousands of records similar to the test case and which i had to do it manually in excel and then upload it back.
create table ow_oper_setup (wo_no varchar2(12),mrk_no varchar2(20),pos_no varchar2(30),mc_code varchar2(60))
insert into ow_oper_setup VALUES ('1270','1270001','W165','IR HO BV ')
insert into ow_oper_setup VALUES ('1270','1270001','W1332','IR BV ')
insert into ow_oper_setup values ('1270','1270001','W1367','RE HO SC BV ')
insert into ow_oper_setup values ('1270','1270001','W389','RE HO SC BV')
commit;
SELECT * FROM ow_oper_Setup;
WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR HO BV
12701270001W1332IR BV
12701270001W1367RE HO SC BV
12701270001W389RE HO SC BV
--i want the output in the following way or the same table data to be replaced as below
WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR
12701270001W165HO
12701270001W165BV
12701270001W1332IR
12701270001W1332BV
12701270001W1367RE
12701270001W1367HO
12701270001W1367SC
12701270001W1367BV
12701270001W389RE
12701270001W389HO
12701270001W389SC
12701270001W389BV
View 12 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
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
Oct 20, 2010
I have a table which has a column that stored concatenated data.
Sample test case is as below:
SQL> create table tst (
2 col1 varchar2(20));
SQL> insert into tst values ('one,two,three');
1 row created.
SQL> commit;
Commit complete.
Is there any way i could write a sql to split the text of this column into rows? Sample output im expecting is as below
col1
-------
one
two
three
View 13 Replies
View Related
May 30, 2013
I am currently doing column values concatenation from multiple rows and then removing duplicates as in the following example:
SQL> select pid
2 , regexp_replace(ltrim(sentence), '([A-Za-z0-9]+,)1+', '1')
3 from ( select pid
4 , seq
5 , sentence
6 from b
7 model
8 partition by (pid)
9 dimension by (seq)
[code]....
but for some reason regexp_replace does not seem to work with clob and I get:
ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
Is it possible to eliminate duplicates in the model before the concatenation?
View 6 Replies
View Related
Nov 26, 2010
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan
View 6 Replies
View Related
Nov 28, 2012
column1 column2 column3 column4
12 Mar-21-2005 BDW blah blah blah
11 Feb-07-2001 ZV ha ha ha
12 Jan-02-2002 YM zuck zuck zuckI want a view that has that data like this:
column1 column2
12 Mar-21-2005 - BDW - blah blah blah; Jan-02-2002 - YM - zuck zuck zuck
11 Feb-07-2001 ZV ha ha haCan you help with SQL ?
I tried to use this Oracle LISTAGG function in the SQL, but got a "string concatenation limit exceeded"
View 3 Replies
View Related
May 4, 2010
find the Test Case below.
--Creation of Table
create table tb1
(ID number(4),
event varchar2(20),
vdate date);
--Inserting Values into the Table.
INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')
[Code]...
--Selecting data from Table.
SELECT * FROM TB1;
ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10
10 rows selected.
how can i display the data as below format using Oracle 9i SQL.
IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10
View 4 Replies
View Related
Apr 10, 2011
tried searching google and this site too, found postings on WM_CONCAT, STRAGG, concat_all, LISTAGG functions by Michel and have experimented with these, but either the syntax is giving me a hard time or i just have not got the concept down.
Trying to get 2 rows into one. Have provided the create statements and insert of data. Also below will show what is returned with a Select i have and what is ideally required.
CREATE TABLE Person_Lang
(
Person_ID NUMBER NOT NULL,
Language_ID NUMBER NOT NULL,
Contact_Name VARCHAR2(255 CHAR),
Main_Phone VARCHAR2(255 CHAR),
Secondary_Phone VARCHAR2(255 CHAR),
[Code]...
Data Returned from Select is:
1 46 905-231-3319 22 Street11 Apt402 Brantford
1 46 905-231-3319 23 Street12 Apt315 MainTown
Ideally what is required back is:
1 46 905-231-3319 22 Street11 Apt402 Brantford 23 Street12 Apt315 MainTown
View 7 Replies
View Related
Mar 1, 2011
I am working on a script in which I want to retrieve multiple rows but I get error ORA-1422.I tried solving it using the following script , but it still gives error.
CREATE OR REPLACE PROCEDURE proc_query
DECLARE
TYPE all_dest IS TABLE OF NUMBER;
destIds all_dest;
BEGIN
SELECT dest_id from sb_packet WHERE src_id = 32;
RETURNING dest_id bulk collect into destIds;
END;
View 3 Replies
View Related
Nov 5, 2010
in eache record we are receiving information from differente city, we pretend to get an output where we have a row for each city (delimited by comma) that we have in column CITY
Input data
SELECT '1001001' as CLIENT_ID, 'LONDON, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1022201' as CLIENT_ID, 'MADRID, OSLO' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1033001' as CLIENT_ID, 'PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION
SELECT '1033004' as CLIENT_ID, 'MADRID, OSLO, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL
Output expected
CLIENT_IDDT_REGCITY
100100105/11/2010 LONDON
100100105/11/2010 PARIS
102220105/11/2010 MADRID
[code]...
View 3 Replies
View Related
Apr 7, 2011
How am i able to combine multiple rows into one row without using cursor if possible. I am currently using SQLPlus.
My current result set:
user id|user name|group name
1|user1|group1
1|user1|group2
2|user2|group1
2|user2|group3
What i wish to achieve:
user id|user name|group name
1|user1|group1,group2
2|user2|group1,group3
View 7 Replies
View Related
Apr 30, 2010
I need to return results for the following query only when there are two or more rows found by the following:
SELECT DISTINCT D.PUBLICATION,
D.ADI,
D.DISTRICT,
D.ACCOUNT,
D.ROUTE,
[code]......
Expected results would be:
1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 5 23-APR-10 48623
1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 4 23-APR-10 48629
View 19 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
Oct 13, 2009
I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)
Here is my query:
Update A set (A.id, A.alt_name, A.min_rank)=
(SELECT B.id,
B.fullname,
MIN(B.nm_rankval)
FROM B,
A
WHERE A.id = B.id
AND A.name <> B.fullname
AND B.nametyp = 'ON'
GROUP BY B.id,
B.fullname)
;
The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)
Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.
View 2 Replies
View Related
Mar 25, 2007
I have to build a select query but its where conditions will be retrieved from a table. I was told that the execute immediate command can handle it.
lets say i have this:
string_var:= 'select field1, field2, field3
from mytable
where' ' || i.condition_selection || ';'
If the above select resuls in a single row, i could do this:
EXECUTE IMMEDIATE string_var INTO var_field1, var_field2, var_field3;
In my case the select will return multiple rows. How do I proceed ?
View 4 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
Sep 14, 2009
My table has the follwoing 3 columns (in addition to others)
Col Name = active ; type=number ; values=1 (true) or 0 (false)
col name start_date ; type=date; format=dd-mmm-yy
col name end_date ; type=date; format=dd-mmm-yy
I need to select all rows where all active=1, start_date=<today and end_date=>today
my sql is: SELECT id, start_date, end_date FROM offers WHERE (active='1' AND start_date<='14-SEP-09' AND end_date>='14-SEP-09');
However the results are not right. Example, the first row returned is: Offer5000312 01-JAN-09 11-DEC-08
This is not correct. Due to the end_date this row should not be part of the results.
View 10 Replies
View Related
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
Sep 9, 2008
I'm getting an error "SQL command not properly ended" with the following complex delete duplicates query.
DELETE FROM A_EMIS_TOXIC_DTL
WHERE exists
(
select dcf.MASTER_AI_ID,
dcf.INT_DOC_ID,
txd.SUBJECT_ITEM_CATEGORY_CODE,
[code]...
View 2 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
May 2, 2012
I have data like :
ID NAME1 NAME2 NAME3
JJ AD MED
VI TIBO PH TIBO
I want output like
ID NAME
JJ AD
JJ MED
VI TIBO
VI PH
VI TIBO
View 4 Replies
View Related
Jun 23, 2006
can i have a pl/sql function that can return multiple rows
may be the syntax will be like
create or replace function multiple() returns ...
begin
select candidateid from tbl_candidateinfo;
..code to return the result of above statement to calling program..
end;
and functions will be called as
select candidateid from .. where candidateid in( select multiple());
View 13 Replies
View Related
Feb 8, 2011
I have two tables. The first contains all Segment information within Oracle i.e;
Table 1
Segment Description Flex_Value
1 North 1234
1 South 1235
1 East 1236
2 Car C001
2 Boat B001
and the second table contains financial data, but only the segment code;
Table 2
Date Segment1 Segment2 Value
01/01/11 1234 C001 10,000
02/01/11 1235 C001 10,000
what I want to return is some of the columns within table two as well as additional columns for the segment descriptions.
Below is an extract of what I have put together, and it does return what I need, but I'm sure there is a much more efficient way of creating the query;
SELECT
b.DATE,
b.SEGMENT1,
b.SEGMENT2,
b.SEGMENT3,
b.SEGMENT4,
b.SEGMENT5,
[code].....
View 6 Replies
View Related
Jun 6, 2013
I need a function that should return output of this query
SELECT b.branding_code, c.name_desc
FROM
development.brandings b, godot.company c
WHERE b.company_id = c.company_id;
This above function return 30 rows and I am not giving any input
Function using cursor,pipeline
View 5 Replies
View Related
Jun 7, 2013
If I return multiple rows says ex:100 rows then what will be data type for that return values.
View 1 Replies
View Related
Oct 1, 2013
i need a query to get dates for last 7 days and each dates should be in one row... but select sysdate from dual..gives one row...
Expected Output Dates: 01-oct-201330-sep-201329-sep-201328-sep-201327-sep-201326-sep-2013
View 3 Replies
View Related
Jul 9, 2012
NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'
File :-mauri.export.ngf
NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc
CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......
find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.
View 6 Replies
View Related
Mar 16, 2011
can we have multiple database version running in a single machine with multiple instances provided there are enough resources.Can we do in RAC only?
View 15 Replies
View Related