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
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
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
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
Apr 19, 2013
I need the data in this below format.
I added the image...
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
Dec 26, 2011
is it possible convert data from forms 6i to pdf format?
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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
May 18, 2011
how can i get the output format from given input table
View 2 Replies
View Related
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
