PL/SQL :: Converting Multiple Column In Single?

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


ADVERTISEMENT

SQL & PL/SQL :: How To Convert Multiple Column Values Into Single Column

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

SQL & PL/SQL :: Replace Multiple Value In Single Column?

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

Fetching Multiple Rows For Single Column

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

SQL & PL/SQL :: Select Multiple Values Into Single Column

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

PL/SQL :: Concatenating Columns (in Multiple Rows) To One Column In Single Row

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

Server Utilities :: To Load Single Column Data Into Multiple Columns

Mar 26, 2012

I want to load single column data into multiple columns.

View 3 Replies View Related

SQL & PL/SQL :: Converting Number Column To Date Column

Dec 25, 2012

I have a partitioned table with ~50 million rows that was setup with a number(10) instead of a date column. All the data in the table is ALWATS in this format YYYYMMDD

CREATE TABLE T1.monthly
(
SEQ_NUM NUMBER(10) NOT NULL,
DAY_DK NUMBER(10) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
[code]........

some sample data

SEQ_NUM DAY_DK
---------- ----------
990 20121225
991 20121225
992 20121225
993 20121225
994 20121225
995 20121225
996 20121225
997 20121225
998 20121225
999 20121225

When I use the exchange partition method the parition is able to move the data from "monthly" table to "mth" table.

desc t1.mth; ### my temorary table
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQ_NUM NUMBER(10)
DAY_DK NUMBER(10)

Than when I try to alter my temp table "mth". I get an error table must be empty to change column types.

alter table n546830.mth modify (DAY_DK date);

Next I tried making my temporary table "mth" a date column. When I an the exchange partition command I get the following error:

alter table t1.monthly exchange partition DEC_2012
with table t1.mth without validation;
alter table n546830.monthly exchange partition DEC_2012 with table n546830.mth without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Method I can use to convert a number(10) to date column and keep the information in a table. Note, I don't care about HH:MM:SS as I never had that information to begin with and would be happy to set that part of the date column to all zeroes "00:00:00"

View 12 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

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

Converting A Column Into Rows

Jul 14, 2012

I have two tables as follows:

TABLE_1
ID ENTRY_DATE VALUE
------- --------------- ----------
1 1-JUN-12 21
1 2-JUN-12 51
1 3-JUN-12 232
1 4-JUN-12 221
1 5-JUN-12 424
.
.
.
.
1 26-JUN-12 52
1 27-JUN-12 0
1 28-JUN-12 247
1 29-JUN-12 528
1 30-JUN-12 489
2 1-JUN-12
2 2-JUN-12
2 3-JUN-12
2 4-JUN-12
2 5-JUN-12
.
.
.
.
2 26-JUN-12
2 27-JUN-12
2 28-JUN-12
2 29-JUN-12
2 30-JUN-12

TABLE_2

ID DAY_1 DAY_2 DAY_3 DAY_4 DATE_5 .......... DAY_26 DAY_27 DAY_28 DAY_29 DAY_30
----- ---------- --------- --------- ---------- ---------- --------- --------- -------- -------- --------
1 21 51 232 221 424 52 0 247 528 489
2

There are millions of DISTINCT ID values in TABLE_1 and corresponding to each ID there are some values for all the days of a month. I need to insert these values in TABLE_2 in the above format.

View 1 Replies View Related

SQL & PL/SQL :: Converting Column Into Row - Command Not Properly Ended

Aug 9, 2010

My requirement with one table which contains 20 columns. Now I want to convert 18 column into rows. I tried with the following query but its throwing sql error is given below.

select * from tmpl_pop_age_range_col_lvl
UNPIVOT
(
quantity FOR product IN (COUNT_0_TO_4 as 'a', COUNT_5_TO_9 as 'b', COUNT_10_TO_14 as 'c',
COUNT_15_TO_19 as 'd',
COUNT_20_TO_24 as 'e',
COUNT_25_TO_29 as 'f',

[code].....

ERROR at line 4: ORA-00933: SQL command not properly ended

View 8 Replies View Related

SQL & PL/SQL :: Multipurpose Varchar2 Column Error When Converting To Date

Aug 16, 2011

i have a varchar2 column containing string values that can be converted to date i.e. ('31-JUL-11') and that column also contains text strings in it. i.e. ('Some string data...')

records whose column value can be converted to date are extractable via where clause (i.e. those rows are associated with some fix number / flag)

now when i try to use to_date function i get the error that

" ORA-01858 a non-numeric character was found where a numeric was expected "

in sql i have added a where clause to only pick rows with flag, but even then it gives the error.

using a subquery in the from clause eliminates the error, but when i create it in a view it again gives the same error.

View 8 Replies View Related

Single To Multiple Records

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

SQL & PL/SQL :: Getting Multiple Rows To Single?

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

PL/SQL :: Multiple Records In Single Row?

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

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 View Related

SQL & PL/SQL :: How To Convert Single Row Into Multiple Rows

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

SQL & PL/SQL :: Multiple Selects In Single Query

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

SQL & PL/SQL :: Transform Single Row Into Multiple Rows?

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

SQL & PL/SQL :: Multiple Tasks For Single SR - Over Partition By

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

PL/SQL :: Multiple IF Else Statement In A Single Select?

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

PL/SQL :: Single Query Return Multiple Value

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

Forms :: Single Datablocks Used In Multiple Frame

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

SQL & PL/SQL :: Multiple Submit Button In A Single Form

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

SQL & PL/SQL :: Get Single Row From Multiple Duplicate Values Of One Field?

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

SQL & PL/SQL :: Update Multiple Table In Single Query

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

Update Multiple Rows With Different Values In A Single Statement

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

Inserting Into A Table By Selecting Multiple Rows Into A Single Row

Jul 27, 2012

I have a flat file as source wherein I am getting values like

Comp_id, Comp_name, ISIN, column_name, column_value

The structure is like this may contain multiple records like Comp_id, Comp_name, ISIN will be same, but column_name will contain the column_name to which its corresponding column_value needs to be populated to.

E.g. of Feed File -

Comp_id, Comp_name, column_name, column_value

1,HSBC,branch_name,HSBC-DELHI
1,HSBC,branch_add,24-Lajpat Nagar
1,HSBC,branch_phone,2322322
2,HSBC,branch_name,HSBC-MUMBAI
2,HSBC,branch_add,24Andheri
2,HSBC,branch_phone,4445221
2,HSBC,branch_postalcode,400023

Target table structure

Comp_id, Comp_name, branch_name, branch_add, branch_phone, branch_postalcode

I need to insert the above data to a table by selecting data from above scenario.

View 10 Replies View Related

SQL & PL/SQL :: Updating Multiple ROWS With Different Values Using Single Statement?

Feb 16, 2011

The requirement I have is :

I have two tables eim_asset and eim_asset1.I want to update the table eim_asset1 using the following update SQL (Or Logic)

update eim_asset1
set emp_emp_login = (select login from s_user where row_id in
(select row_id from s_emp_per where row_id in
(select pr_emp_id from s_postn where row_id in
(select position_id from s_accnt_postn where ou_ext_id in
(select row_id from s_org_ext where row_id in
(select owner_accnt_id from s_asset where owner_accnt_id is not null)))))

It gives me the ORA error : ORA-01427:single-row subquery returns more than one row.know why I am getting it, because of the one-to-many relationship between owner accounts and their assets.

View 1 Replies View Related

Windows :: Installing Multiple Version Of Client On A Single PC?

Sep 17, 2010

installing Oracle 9i and Oracle 10 g client on a single PC.

What are the things I should take not of for the installation?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved