SQL & PL/SQL :: Query On Single Column To Display As Rows

Aug 5, 2010

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.

View 10 Replies


ADVERTISEMENT

Fetch Records In Single Select Query And Display

Mar 15, 2011

I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.

Name id dept_no
Ram 101 10
20
30
Ani 201 10
20

View 1 Replies View Related

PL/SQL :: Convert Rows To Single Column

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

SQL & PL/SQL :: Display Values In A Column Instead Of Rows

Mar 10, 2011

Consider the following (example)table.,

TABLE_A
------------------------------
ID DEPT CRS
------------------------------
1 CS CS_100
2 SCIENCE SCI_150
3 MATH MATH_400
4 HISTORY HIS_110

[Code]...

To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,

--------------------------------------------
NO DEPT CRS
--------------------------------------------
1 MATH MATH_400, MATH_550, MATH_230
--------------------------------------------

instead of.,
--------------------------
NO DEPT CRS
---------------------------
1 MATH MATH_400
2 MATH MATH_550
3 MATH MATH_230
---------------------------

View 2 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 :: Append More Rows In Single Clob Column

Apr 9, 2013

I have a query which returns nearly 20k rows, as per the requiremnet we need to append all these rows in specific format and insert into single clob column.in the below procedure test_clob.textt is clob field.

CREATE OR REPLACE PROCEDURE pro_test
v_mas_seq NUMBER (9);
v_gov_total NUMBER (20, 2);
v_emp_total NUMBER (20, 2);
v_text_exp CLOB;
v_pageaccess VARCHAR2 (15);
v_dto NUMBER (7) := 4011486;
v_batchno NUMBER (20) :=
[code]....

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

Query To Exclude All Rows Based On Single Row Filter?

Jun 10, 2011

I'm using Oracle 10g.

Question: How can I write query to return just ID only if all the codes for that ID end in 6.

If I use
SQLselect id from table_a where code like '%6'
then I also get ID=1, which I don't want.

TABLE_A
ID code
=======
1 100
1 106
2 206
3 306
3 336
4 400

Desired Result
ID
==
2
3

View 1 Replies View Related

SQL & PL/SQL :: All Selected N Consecutive Rows Retrieved In Single Query

Nov 11, 2011

I have table T with 50,000 rows

create table T
(student_id number,
class_id number,
quiz_id number,
marks number)

some sample rows like

INSERT INTO T VALUES (1,1, 1, 50);
INSERT INTO T VALUES (2,2, 2, 40);
INSERT INTO T VALUES (3,1, 3, 34);
INSERT INTO T VALUES (1,1, 4, 10);
INSERT INTO T VALUES (1,1, 5, 30);
INSERT INTO T VALUES (1,1, 6, ‘29);
INSERT INTO T VALUES (3,2, 7, 34);
INSERT INTO T VALUES (3,2, 8, 33);
INSERT INTO T VALUES (3,2, 9, 56);
INSERT INTO T VALUES (1,1, 7, 90);
INSERT INTO T VALUES (2,2, 8, 0,);
INSERT INTO T VALUES (1,1, 8, 80);
INSERT INTO T VALUES (2,2, 8, 65);
INSERT INTO T VALUES (1,1, 9, ‘34);
INSERT INTO T VALUES (2,2, 9, 11);

each student belongs to one class_id. each student participates in many quizes. each quiz has its unique id. each student can appear once in a quiz_id

I am doing the below analysis and query:

1. with below query I am finding which student_id had most marks in any 3 successive quizes (see the 3-1 part below) in the query..

SELECT QUIZ_ID,
STUDENT_ID,
SUM (MARKS)

[Code]....

SQL> /

QUIZ_ID STUDENT_ID CONSECMARKS
---------- ---------- -----------
7 1 170
6 1 166
8 1 129
5 1 106
8 3 89
8 2 76
3 3 68
7 3 67
8 2 65
1 1 60
9 3 56
9 1 49
2 2 40
4 1 40
9 2 11

15 rows selected.

With above query, I can play around and find for any 'n' number of consecutive quizes, like marks in 2 consecutives quizes, 3, 4 and so on but for each 'n' value I've to run a seperate query mentioning (2-1) or (3-1) or (4-1) and so on..

since my table is big and there are about 400 quizes so what I want to find out is for each 'n' consecutive quiz (from 1 to 400) which student had most marks for each consecutie 'n' quiz. Like in 1 (consecutive) quiz which student had the highest marks and then 2 conseuctive quiz who had most marks and then in 3 consecutive quiz who had most marks and so on till 400 consecutive quiz who had most marks... rather than running query for each 'n' value seperately i want a single query that can give me a summary of most marks in each n consecutive quizes...

my sample output is:

Nth consecutive quiz student_id sum(marks)
1 1 90
2 1 170
3 1 246
4
.
.
.
100
.
.
200
.
.
300
.
400 ? ?

Is this possible to get the above output from one single query? If there are two or more students with equal most marks for any 'n' conseutive quizes then both should come in the summary.

View 7 Replies View Related

SQL & PL/SQL :: Query To Convert Data In Single Column?

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

SQL & PL/SQL :: Update Two Table Column In Single Query

May 25, 2012

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 ;

View 1 Replies View Related

SQL & PL/SQL :: Display Office Address / House Address And Emp ID In Single Query?

Feb 23, 2013

I have two tables emp , emp address as below

emp table :
emp_id emp_name,
1 rajesh
2 suresh

emp address table :

emp id emp_addresstype emp address
1 O kukatpally
1 H Hitech
2 O Kolkata
2 H hydar nagar

I need the query to display the Office address & House address & emp id in single query

ex: 1,Kukatally,hitech
2,kolkata,hydarnagr

View 3 Replies View Related

SQL & PL/SQL :: Query To Display Repeated Column Values As Null?

Dec 13, 2012

I have table with the values as below.

C1C2C3C4
NAMEJOHN10ABC
NAMESMITH30DEF
NAMEROBERT60XYZ

I dont want to print the repeated value(NAME) of C1 multiple times as below.

C1C2C3C4
NAMEJOHN10ABC
SMITH30DEF
ROBERT60XYZ

I could do it using the below query using union with the rownum.

select * from (
select rownum rn, c1,c2,c3,c4 from table_new
) where rn =1
union
select * from (
select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new
) where rn between 2 and 3

Is there any other way of displaying using a single sql query.

View 17 Replies View Related

SQL & PL/SQL :: Query To Convert Rows Into Column

Dec 11, 2009

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.

View 7 Replies View Related

SQL & PL/SQL :: Query To Retrieve Number Of Not Null Rows For Each Column?

Jul 7, 2011

I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.

ex:

the output should look like this.

owner schema table_name total# recs in the table, column_name,
------ ------ ---------- ------------------------- -----------

# of records not null # of records null
---------------------- --------------------

View 12 Replies View Related

SQL & PL/SQL :: Write A Query To Combine Amount1 And Amount1 Into Single Column Amount?

Aug 3, 2011

How 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]....

View 7 Replies View Related

SQL & PL/SQL :: Display A Value In Between Single Quotes?

Nov 18, 2011

how to write a select query to display a number column value in between single quotes. Ex:i have a emp table in that sal column is a number type.

Ex:
select sal from emp;
sal
----
1234
231
3456
3211
23445I want to display the same above result in b/w single quotes.

ex:
sal
----
'1234'
'231'
'3456'
'3211'
'23445'
for this i need to write a query,

View 5 Replies View Related

Forms :: Display Report With Parameter Using Single Button

Jul 14, 2008

From my form with a single button i need to display a report with a paramter. Report is displaying fine but the parameter is not getting passed. I did the following behined my button i add this

DECLARE
repid REPORT_OBJECT;
v_rep VARCHAR2(100);
rep_status VARCHAR2(20);
plid ParamList;
vParamValue number := 20;

[code]...

If i remove the where condition the whole table data start appearing in the report but adding where condition is not accepting 20 as i send from the FORM Button. And the report with blank data appeared with column headings.

View 5 Replies View Related

Reports & Discoverer :: Formatting - Display Value Of Field In Single Line

Oct 20, 2010

I have developed one report but need one formatting suggestion. There is one field called "DESCRIPTION", I want the value of this field to be displayed in a single line. Now the big values are wrapped into multiple line.

Now,

DISPLAY
----------------
This is a Oracle
Report.

I want,
DISPLAY
------------------------
This is a Oracle Report.

Maximum size of "DISPLAY" field is VARCHAR2(240).

View 3 Replies View Related

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 :: All Rows Value To Single Row

Jul 15, 2011

i want to all rows value into single rows.

for example;

Input data:
Sec_SSC_ID Column_nameAs of dateOld valu New Value
IBM Mat_dt 10/10/20101/1/2001 1/1/2002
IBM Bid Market 10/10/201075 85
IBM asset_nm 1/1/2011International IBM
MSFT asset_nm 1/2/2011Microsoft Intel
MSFT Bid Market price 1/1/201189 90

Output data

I have attached sample output file with it.

how to get this via sql?

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

SQL & PL/SQL :: Create Single Row From 2 Rows?

Oct 19, 2010

creating a single row from 2 rows based on the first column which is not unique.

CREATE TABLE RELATIONSHIP
(
RelationshipID number not null,
DepartmentID number not null
)
INSERT INTO RELATIONSHIP VALUES (1,1)
INSERT INTO RELATIONSHIP VALUES (1,2)
INSERT INTO RELATIONSHIP VALUES (2,3)
INSERT INTO RELATIONSHIP VALUES (2,4)

Im trying to get something like

RelationshipID DepartmentID1 DepartmentID2
1 1 2
2 3 4

There are a few more tables that would need joining, which I can provide but at the moment I'm struggling with this part, I know there is a problem with the formatting here but not sure how to get tabs correctly

View 11 Replies View Related

Display Different Number Of Rows?

Jan 23, 2009

i have a table displaying location, cost, inner_num and rownum ordered by the descending order of the cost.

the question is: display locations of the highest 10 costs. if the cost of 11th row is the same as 11th, then display 11 rows.

View 3 Replies View Related

SQL & PL/SQL :: How To Display All The Rows In A Table

Sep 8, 2010

I am bit confused to display all the rows in a column using stored procedure like

In sqlserver:
create procedure test
as
begin
select * from table;
end
in sqlserver it is working perfectly.

In oracle:
create or replace procedure test
is
begin
select * from table;
end;

But in oracle i am getting an error like select * into something like that.I understand the error. i.e. i need to move the column values into variables.if it a single row then there will be no problem.i want to display all the rows in the table at one time.i can not use cursors.

View 3 Replies View Related

PL/SQL :: Display All Columns In Different Rows?

Oct 9, 2013

i want to display all the columns of emp table in rows.O/P should be like below:---------------------------------------------Columns------------empnoenamesaldeptnomgr

View 7 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 :: Fetch All Rows Into Single Variable

Feb 21, 2012

i want to load all rows of single columns into one variable. After that, that variable will use in any side of the pl sql block.

SET serveroutput ON
DECLARE
CURSOR CUR_DATA
IS
SELECT DAS_SECURITY_CODE FROM SECURITY_TYPE WHERE ASSET_TYPE='DERIVATIVES';
rec_data CUR_DATA%rowtype;

[Code]....

how to resolve this. above query,variable holds last value which fetched from cursor.

View 9 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 :: Procedure To Display All The Rows From A Table?

Mar 25, 2013

how to create a procedure to display all the rows in a table.

I wrote the below PL/SQL but im hitting as below.

DECLARE
howmany NUMBER;
some_full_name login_user.full_name%TYPE;
some_login_id login_user.login_id%TYPE;
some_users login_user%ROWTYPE;

[code]....

error :

Error report:
ORA-06550: line 3, column 19:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 3, column 19:
PL/SQL: Item ignored
ORA-06550: line 4, column 18:

[code]....

View 19 Replies View Related







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