SQL & PL/SQL :: Display Data In Grouping Format

Oct 13, 2010

I have a query on displaying data as per my requirement. I have created a table called sales it has four columns

create table sales(country,state,district,sales);
and am inserting some same data

insert into sales('india','TN','Chennai',100);
insert into sales('india','TN','KPURAM',120);
insert into sales('india','TN','Bangalore',35);
insert into sales('india','ANDR','Guinder',100);
insert into sales('india','ANDR','Nellai',76);
insert into sales('london','city-a','xstreet',89);
insert into sales('london','city-a','binroad',100);

select * from sales;

country state district sales
india TN chennai 100
india TN KPURAM 120
india TN Bangalore 35
india ANDR Guinder 100
india ANDR Nellai 76
london city-a xstreet 89
london city-a binroad 100

the data is displayed in this format. How i am trying to display data.

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: To Display Data In Range Format

Apr 25, 2012

I have a table where multiple combination of records are store and i want to display data in range format as below, there is any way to group data as below.

create table ot_shop_Rec ( item varchar2(12), it_name varchar2(20),rev number, qty number)
drop table ot_shop_rec

insert into ot_shop_rec values ( '1018001-1001', 'COL',0,10);
insert into ot_shop_rec values ( '1018001-1002', 'COL',0,10);
insert into ot_shop_rec values ( '1018001-1001', 'GRID',0,10);
insert into ot_Shop_rec values ('1018001-1003','COL',0,10);

I WANT THE OUTPUT IN REPORT LIKE

ITEM RANGE DESC QTY REV

1018001-1001 - 1018001-1003 COL 30 0
1018001-1001 GRID 10 0

View 5 Replies View Related

PL/SQL :: Return XML Format With Grouping

May 14, 2013

Allow me to preface this with the notice that I am not familiar with XML outside of its hierarchical structure, and am not familiar with what you can do with it using formatting.

As an example, let us say you have the following table:

Object_Type | Object_Name | Descriptor |
------------------------------------------------------------
Fruit | Apple | Crunchy |
Fruit | Orange | Sour |
Utensil | Pencil | Wooden |

Now let's say you want to query this table to return an XML format, which will be used in a web site to display the information, and you want to group the display by Object_Type, so that you want an XML format like this:

<Object Group>
<Object Type>Fruit</Object Type>
<Object>
<Object Name>Apple</Object Name>
<Descriptor>Crunchy</Descriptor>
[code]........     
  
However, from what I can tell, using the XMLELEMENT function, it appears the closest I can get is following:

SELECT XMLELEMENT("Object Group",
XMLELEMENT("Object Type", object_type),
XMLELEMENT("Object",
XMLELEMENT("Object Name", object_name),
XMLELEMENT("Descriptor", descriptor)
)
)

FROM object_tbl;

<Object Group>
<Object Type>Fruit</Object Type>
<Object>
<Object Name>Apple</Object Name>
[code].........          

Is it possible to group it in a way so that Apple and Orange end up in the the same <Object Group>? Or is that meaningless and such grouping can be done on the web site itself by formatting the XML?

View 5 Replies View Related

SQL & PL/SQL :: Display Data In Required Format By Select Query?

Dec 12, 2012

i like to display a Table data like the below format,

Output:

EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
00094 122 153 145 224 245 545 114 544 444 111 555 222
00095 233 123 145 224 205 545 114 444 444 111 555 222
00096 163 123 145 224 215 545 114 551 444 111 555 222
00097 163 0 145 224 215 545 114 551 444 111 555 222
conditions:

where condition:
where year = 2007

Table Structure:

create table HR_PAYSLIP
(
EMP_NO VARCHAR2(6) not null,
YEAR NUMBER(4) not null,
MONTH NUMBER(2) not null,
BASIC_PAY NUMBER(9,2),
)

Insert Command;

INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY)
VALUES(00046, 2007, 1, 2314);

Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month

So how to write a Query for that?

View 9 Replies View Related

Reports & Discoverer :: Display Data Vertically (landscape Format)?

Apr 19, 2013

I need the data in this below format.

I added the image...

View 2 Replies View Related

SQL & PL/SQL :: Grouping Of Data

Aug 19, 2013

I have a requirement to get the records group wise.Ex: For each departments, i need to get the employee details as a coma seperated.It means that the output must have the department name in first column and the second column must contain all the employees in that particular department (As a coma seperated).

View 3 Replies View Related

PL/SQL :: Grouping Of Data

Feb 10, 2013

How can I get the grp_id for unique combination of manager and department, grp_id should be created on asc order of manager_id.
In this example manager_id 100 is minimum, so it should be grp 1 and all the employees with that manager_id should be in grp_id 1, for manager_id 114 grp_id should be 2.

If, there is manager_id 117, it should create grp_id 3.

To get grp_num ,I can use row_number() over (partition by department_id,manager_id order by employee_id) grp_num

I am looking for an update statement for this issue.

Oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

CREATE TABLE HR.EMPLOYEES_2
(
  EMPLOYEE_ID         NUMBER(6),
  FIRST_NAME          VARCHAR2(20 BYTE),
  LAST_NAME           VARCHAR2(25 BYTE),
  EMAIL               VARCHAR2(25 BYTE),
  PHONE_NUMBER        VARCHAR2(20 BYTE),

[Code]....

Expected result
----------------
EMPLOYEE_ID    SALARY    MANAGER_ID    DEPARTMENT_ID    GRP_NUM    GRP_ID

114              11000        100          30              1              1
115              3100         100          30              2              1
116              2900         114          30              1              2
117              2800         114          30              2              2
118              2600         114          30              3              2
119              2500         114          30              4              2

View 8 Replies View Related

PL/SQL :: Grouping Dynamic Data

Aug 29, 2012

In my schema the employees table has a number of 55 rows in department_id 30.

How can I spit the employees table into views group by department_id as

- one view with no more than 55 rows (this view will contain only a department)
- another view with more departments but whose number of rows is not > 55 but can contains 2 department_id (e.g.: 9, 10 and the sum of these rows is 43 but if I would like to bring another department the rows count will be > 55)

View 5 Replies View Related

Data Set - Grouping By ITEM NO And DATE?

Jun 14, 2012

Actually I have a table with the following data:
--------------------------------------------------
DATE ITEM NOFEE TYPEAMOUNT
--------------------------------------------------
1/1/20121234561 $0.50
1/1/20121234562 $0.40
1/1/20121234563 $0.30

[code]...

I would like to have a data set like this: grouping by ITEM NO & DATE
----------------------------------------------------------------------
DATE ITEM NO1 2 3 4
----------------------------------------------------------------------
1/1/2012123456$0.50 $0.40 $0.30 $0.20
2/1/20121234567$0.50 $0.40 $0.30 $0.20
3/1/201212345678$0.00 $0.40 $0.30 $0.20

if you see, from the third column in the result set, each fee type becomes different columns.

View 1 Replies View Related

SQL & PL/SQL :: Update Display Name Based On Format?

May 3, 2013

I have a table which has columns like First_Name,Last_Name and Display_Name. Now the every entry in the table have first name and last name populated .

I would like to populate the display name based on the format Display Name = First Name+ " " + Last Name.

For eg. If the first name John and last name is Doe , then the display name should be John Doe.write such a queries which dynamically picks up all the rows and populate the display name.

View 12 Replies View Related

Grouping Data By Continuous Intervals Of TimeStamp

Nov 26, 2010

I am having past data in a table say for one year. each row is having a timestamp column. Now i need to calcute avg of data for every time interval betwwen start date and end date.

time interval can be in minute, hours, days or months..in case of hours,days and months , i need to convert to minutes and group based on that interval.

View 11 Replies View Related

Wrong Number Format Display Using DBLink

Jan 7, 2013

I have an issue trying to execute some queries using a dblink. When i run any query with numeric fields only display 4 digit and int the source database the fields have 5 digit. The dblink work between ans MSSQL database to an Oracle Database

Example:
MSSQL
select cardnumber from card
cardnumber
19121
19122

Oracle (with dblink)
select cardnumber from card@dblink1
cardnumber
1912
1912

View 1 Replies View Related

PL/SQL :: Date Format - Display Fraction Of Seconds

Oct 8, 2012

select to_char(SYSDATE,'MMDDYYYYHHMISS') from dual;

Ouput:
10082012010338

It is giving me correct output, but i want to display even the Fraction of seconds, how can i?

I have tried this..but not working..

select to_char(SYSDATE,'MMDDYYYYHHMISSFF') from dual;

Output: Error.

View 4 Replies View Related

SQL & PL/SQL :: Display Time Format In Oracle By Create View

Nov 16, 2011

I am not able to find any information regarding inserting data with timestamp format without putting the date.

This is what i want to do:

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH24:MI TZR';

I create the column data type as timestamp with time zone. This is value i have inserted:

Insert Into TEST Values('05-OCT-2013 01:00 +08:00','05-OCT-2013 23:00 +08:00','SCIENCE);

And now i wish to display it in 'HH24:MI TZR' only after i deduct both of the time. This is the view i have created:

Create View TESTRECRDS As Select (STARTTIME- ENDTIME)*24 As DURATION, Science;

But when i select it display as weird timing because it would default in 'DD-MON-RR HH24:MI TZR' format.

BUT IF i create the view and display as 'HH24:MI TZR', it would gives me error:

Create View TESTRECRDS As Select To_Char(STARTTIME- ENDTIME, 'HH24:MI TZR')*24 As DURATION, Science;

Error would be invalid number. How can i display only 'HH24:MI TZR' after subtracting?

View 3 Replies View Related

Reports & Discoverer :: Change Format Of Figure Display On Bar Graph?

Aug 18, 2009

how i can change format of figure display on bar graph(data labels) in oracle 10g graphical reports .i want to change format fom 99.99 to 9999 .

View 1 Replies View Related

SQL & PL/SQL :: Format Table Data

Jul 4, 2013

I have data in bellow format...

NAMEPROMPT CHARACTER1 CHARACTER2 CHARACTER3

MOAAPPROVER15100160 5100165 MUNGAI
MOAAPPROVER25100160 5100165 MUNGAI
MOAFINAL APPROVER5100160 5100165 MUNGAI

so all values which are in CHARACTER1 column belongs to APPROVER1,CHARACTER2 belongs to APPROVER2,and CHARACTER3 belongs to FINAL APPROVER.

Now i want to display records in bellow format

NAMEPROMPT CHARACTER1 CHARACTER2 CHARACTER3
MOAAPPROVER1 5100160 5100160 5100160
MOAAPPROVER2 5100165 5100165 5100165
MOAFINAL APPROVER MUNGAI MUNGAI MUNGAI

View 6 Replies View Related

SQL & PL/SQL :: How To Change Column Data In Row Format

Jun 1, 2012

I want to get the following format of data in row format using PLSQL. I want to do that in using a shell script also.

Suppose I have the data like this

123
45
2
789

how to write it in PLSQL as follows:
1427
25 8
3 9

View 3 Replies View Related

Reports & Discoverer :: Data To Be Taken In Different Format

Dec 2, 2012

I have one table storing all the information about employye, i have to develop three different reports based on the same table with different groupings , is there a easy way to call each format differently using single interface ,something like ref cursor.I dont want to create two or 3 different reports , instead choose the format.

CREATE TABLE OT_JOB_DET
(
JT_TXN VARCHAR2(12 BYTE),
JT_NO NUMBER,
JT_DT DATE,

[Code].....

View 4 Replies View Related

Forms :: How To Convert Data From 6i To PDF Format

Dec 26, 2011

is it possible convert data from forms 6i to pdf format?

View 4 Replies View Related

SQL & PL/SQL :: Print Data From A Clob In XML Format?

Sep 11, 2013

I want to print data from a clob in XML format and use the following

PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(32767);
cnt NUMBER;

[Code]...

However the length of the clob is 13832630 which is too large for a VARCHAR2. Thus my output of line is cut off at 4000 characters. How can i increase this... Do i overlook something here in my code?

View 13 Replies View Related

SQL & PL/SQL :: Query To Get Data In Desired Format?

May 29, 2012

CREATE TABLE DATA1
(
ID NUMBER(6),
DAT_ID NUMBER(6),
RNK NUMBER(2)
);
Insert into DATA1
(ID, DAT_ID, RNK)

[code]....

now after having this data from "data1" table , we need to get the row from "data2" table.in "data2" table there are total 6 combination on basis of "POS,ORDER" [ there are only 2 "ORDER" i.e. 'F' and 'S' , where as POS value can be changed, BUT THE "POS,ORDER" COMBINATION WILL HAVE ONLY 6 UNIQUE COMBINATION. ] so, for "POS and DAT_ID" combination we need to get the lowest rank data first, if that is not present then get the other rank given in "DATA1" table and so on and if no rank is present then select the NULL row row data from "DATA2" table for ex: in DATA1 table for count(*)>1 and id=1, we have data as

--------------
IDDAT_IDRNK

11231
11242
11253
--------------

so, in "DATA2" table, first we will see for "POS and ORDR" combination which DAT_ID is present, i.e. in case od POS=11 and ordr=F, we will select

111231FD1

as it is having lowest rank in "DATA1" table and it is present in "DATA2" table,

for POS=12 and ordr=F, we will select

121242FD1

as we don't have "123 and 1" in "DATA2" table so we will select the next rank given in "DATA1" ( i.e. 124 and 2 ), similarly , for POs=31 and ordr=S, we will select, as this is the next available rank and DAT_ID present in "DATA2" table

1D131S1253

and if there is no rank present from "DATA1" table in "DATA2" table then we will select the NULL row, i.e. for POS=21 and ordr=F, we need to select :

1D121F

"there will be 6 row for each id"

the output we want is :
-----------------------------------------
IDNAMEPOSORDER DAT_IDRNK
1D111F1231
1D112F1242
1D121F
1D12321S1231
1D2322S1242

[code]....

View 4 Replies View Related

XML DB :: Generating XML Data From A Table In XML Format

May 3, 2013

generating an xml file using the below data.Table name is T_Data having 4 columns as given below with some data.

Region Divison District Store
---------- ----------------------- ----------- -----------
Northwest Northern California San Jose SJStore1
Northwest Northern California San Jose SJStore2
Northwest Northern California North LA LAStore1
Northwest Northern California North LA LAStore2
Northwest Northern California North LA LAStore3

I want to generate an XML File using SQL/XML Functions and the XML File should look like as given below.

<Region>
<Region Name>Northwest</Region Name>
<Divison>
<Division Name> Northern California </Division Name>
<District>
[code]...

View 3 Replies View Related

Data In Date Format For In Time

Oct 26, 2006

i have data in date format for in time actually it should be 9:30 am and out time at 2:30 pm

SELECT MIN(IN_TIME) AS IN_TIME,MAX(OUT_TIME) AS OUT_TIME FROM EMP_ATND_DETAIL AS ET, EMP_ATND_INFO AS EAI
WHERE ET.EMP_ATND_INFO_ID=EAI.EMP_ATND_INFO_ID AND ATND_DATE = '2006-10-25' AND EMP_ID =24

+---------------------+---------------------+
| IN_TIME | OUT_TIME |
+---------------------+---------------------+
| 2006-10-20 09:30:00 | 2006-10-13 01:30:00 |
| 2006-10-24 02:30:00 | 2006-10-13 06:30:00 |
| 2006-10-24 09:30:00 | 2006-10-13 01:30:00 |
+---------------------+---------------------+

i have should get 9:30 am because that is lessser then 2:30 pm

do i have

SELECT name, emp_id,
min(to_char(IN_TIME, 'DD/MM/YY HH24:MI')) in_time,
max(to_char(OUT_TIME, 'DD/MM/YY HH24:MI')) out_time,
FROM EMP_ATND_DETAIL

View 3 Replies View Related

SQL & PL/SQL :: Data Display

Jul 26, 2013

I have a requirement where i need to display like

Consider EMPLOYEES table and If an employee 'A' joined in Jan month then he should come under JAN, if employee B and C joined in MARCH month than both has to come under MAR and so on..

OUTPUT:
JAN FEB MAR APR MAY JUNE JULY AUG SEP OCT NOV DEC
A B D E
C

is this possible.????

View 14 Replies View Related

SQL & PL/SQL :: Extracted Data Is Displayed In Wrong Format

Feb 14, 2013

I use SQL to extract data from Quality Center (QC) to excel. I have a field type String. It contains the following values.

1) 1161, 1162, 1163
2) DHM, 162
3) DTH, 163
etc

But when i extract this to excel the data is displayed as
1) 116111621163
2) DHM, 162
3) DTH, 163

The value in the first row is displayed with out commas. How to extract the data as it is in the field?

View 7 Replies View Related

SQL & PL/SQL :: Sending Table Data In HTML Format

Jul 11, 2008

I need to send data (mail it ) which is stored in my Oracle Database through PL/SQL in HTML table format.

1. how to do it?
2. If YES, throw some light on it ?

View 12 Replies View Related

SQL & PL/SQL :: Format For Accessing Data From Other Database Or Schema?

Aug 25, 2011

Can we use this format for accesing data from other DB or Schema?

In From clause

database_name.schema_name.table_name

View 7 Replies View Related

SQL & PL/SQL :: How To Get Data From A Table In Date And Time Format

Jul 24, 2013

how to get data from a Table in Date and Time format? Curently I'm using To_TIMESTAMP for a date range.

Here is the code Snippet -

Select *
FROM TABLE_Name
WHERE COUNTRY= 'IN'
AND CALC_DATETIME between TO_TIMESTAMP('2013/06/05', 'YYYY/MM/DD HH:MI:SS') AND TO_DATE ('2013/07/08', 'YYYY/MM/DD HH:MI:SS')

View 39 Replies View Related

SQL & PL/SQL :: Output Data Format From Input Table?

May 18, 2011

how can i get the output format from given input table

View 2 Replies View Related

SQL & PL/SQL :: Format Data While Inserting Into Timestamp Field

Mar 24, 2011

I am trying to insert values in the timestamp field

system@id.world> create table t(t1 timestamp,t2 timestamp, t3 timestamp, t4 date);

Table created.

system@id.world> insert into t values(timestamp'2011-03-24 11:03:00.05','12-mar-2011 11.03.00.055',systimestamp,localtimestamp);

1 row created.

system@id.world> insert into t values(timestamp'2011-03-24 14:03:00.05','12-mar-2011 14.03.00.055',systimestamp,localtimestamp);

1 row created.

system@id.world> select t1 from t;

T1
---------------------------------------------------------------------------
24-MAR-11 11.03.00.050000
24-MAR-11 14.03.00.050000

system@id.world> select t2 from t;

T2
---------------------------------------------------------------------------
12-MAR-11 11.03.00.055000
12-MAR-11 14.03.00.055000

system@id.world> select t3 from t;

T3
---------------------------------------------------------------------------
24-MAR-11 11.29.04.491927
24-MAR-11 11.29.17.085396

system@id.world> select t4 from t;

T4
---------
24-MAR-11
24-MAR-11

system@id.world> select * from nls_instance_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT
NLS_DATE_LANGUAGE

[Code]...

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

system@id.world> select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_TERRITORY AMERICA
NLS_CURRENCY $

[Code]...

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

[Code]...

20 rows selected.

system@id.world> show parameter nls_timestamp_fo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_timestamp_format string
system@id.world> create table newt(t1 timestamp);

Table created.

system@id.world> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
24-MAR-11 11.31.07.667296

system@id.world> insert into newt values('12-jan-2010 11:00:00:068801');
insert into newt values('12-jan-2010 11:00:00:068801')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

system@id.world> insert into newt values('12-jan-2010 11.00.00.061');

1 row created.

system@id.world> insert into newt values('12-jan-2010 15.00.00.061');

1 row created.

system@id.world> select * from newt;

T1
---------------------------------------------------------------------------
12-JAN-10 11.00.00.061000
12-JAN-10 15.00.00.061000

system@id.world>

My questions are

1) what is significance of '.' here? insert into t values(timestamp'2011-03-24 11:03:00.05','12-mar-2011 11.03.00.055',systimestamp,localtimestamp);

2) while inserting using "values(timestamp'2011-03-24 11:03:00.05'" where this yyyy-mm-dd hh24:mi:ss.FF format comes from?

3)what is role of nls_timestamp_format of nls_database_parameters in this? how it allowed me to insert value "'12-mar-2011 14.03.00.055'" which has hh24 format?

4) Does the format of localtimestamp is decided by nls_database_parameters?

Note : I have not set nls_date_format in my session

I tried to read the link below but could not understand above

[URL]....

View 1 Replies View Related







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