I am joining two oracle provided basic tables emp and dept and want to show result as a department detail and followed by employee detail belong to that.
Standard result of joining two tables looks like below:
deptno dname empname
10 ACCT SCOTT
10 ACCT MILLER
20 SALES JOHN
20 SALES XYZ
30 FINANCE AAA
Now I need the output as below as per a report requirement.
entity_type name/no
DEPT 10
EMP SCOTT
EMP MILLER
DEPT 20
EMP JOHN
EMP XYZ
I am using oracle 10g release 10.2.0.1.0. Can I use oracle analytic function here?
How do you query the output from the 'pivot xml' sql?
Just as an example, How can you query the output(xmltype) from this sql so that it is flattened into a relational type view?
select * from ( select owner, count(*) as owner_segment_count from dba_segments where owner in( 'OLAPSYS','ORDSYS','QDATA','SYS','SYSMAN','SYSTEM','XDB' ) group by owner ) pivot xml( sum( owner_segment_count ) as owner_segment_count for owner in( any ) )
CM: removed unescessary formatting. Personally I find size 1 hard on the eyes. Also added [code] tags,
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; -- TAKE ALL JOB TITLES FROM JOBS CURSOR JOBSCUR IS SELECT * -- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID FROM TARGET_UCM ; [code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
Is pivot the right command to use? If so, how do I do this? Most pivot examples I've looked at use an aggregate like SUM, which is not really want I am trying to accomplish here.
I'm trying to use a PIVOT on the following data set:
ID STATUS_DESC PAY_STATUS PAID_DATE TRANSACTION_TYPE TRANSACTION_DESC DEBIT TOTAL -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 9876 In Progress 2nd Payment Made 11-DEC-12 19.38.57 Card Payment Payment 2 349 349 9876 In Progress 2nd Payment Made 06-DEC-12 14.33.57 Card Payment Payment 1 100 100
However I'm still getting two rows as per the below. Ideally all data should be on a single row.
ID STATUS_DESC PAY_STATUS PAYMENT_1_DATE PAYMENT_1_AMT PAYMENT_2_DATE PAYMENT_2_AMT TOTAL -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 9876 In Progress 2nd Payment Made 06-DEC-12 14.33.57 100 100 9876 In Progress 2nd Payment Made 11-DEC-12 19.38.57 349 349
I have constructed my pivot using the following on the outer select:
PIVOT (MAX (insert_timestamp) AS paid_date ,SUM (debit) AS amt
FOR transaction_desc IN ('Payment 1' AS payment_1, 'Payment 2' AS payment_2)) ;
I've used MAX to pivot the date and also tried using NVL on the insert_timestamp but still no luck.
I have a requirement to write a single sql query where i can generate the pivot report. Found some of the examples in Google search. But here we are hard coding the values if it is limited like month in this example.
i want to write similar query to represent the amount based on product type , i have around 200 types of products. I can't write case/ decode statement those many times.
query which will produce the output in pivot format , dynamically depending the number of values.
select Product, sum(case when Month=�Jan� then Amount else 0 end) Jan, sum(case when Month=�Feb� then Amount else 0 end) Feb, sum(case when Month=�Mar� then Amount else 0 end) Mar from Sales group by Product
I have a table tab1 which contains columns Cust, ProdSeq, StartDat, EndDat, AttrId, AttrValue. I can have different attributes represented by attrId (say 1,2,3,4..etc.,) and its value by AttrValue respectively.
My question is for a given a customer, prodSeq and date range say 01-Jan-2013 to 31-Jan-2013, I want a report like below
with t1 as ( select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual union all select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual union all select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual) select param_name,param_type,param_value from t1;
desired output:
need output in a row in three different columns
param_value 01-01-1970 31-12-9999 30
I tried below query
SELECT * FROM ( with t1 as ( select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual union all select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
I am trying to produce results in pivot format. I want to know how many entries per day per month. In other words how many entries on a Monday for each month, how many on a Tuesday for each month and so on.
The main problem I have is that the date column in the database uses UNIX time stamp. I have managed to get the results I want for a particular month. This code selects the entries for last month.
What I need is for another column at the start of the results to give the month so I end up with 12 rows, 1 for each month.
I did try to group by PENTERED(which is the unix time stamp column) select pentered, max(decode(pdayno, 1, cnt, null)) Sunday, max(decode(pdayno, 2, cnt, null)) Monday, max(decode(pdayno, 3, cnt, null)) Tuesday, max(decode(pdayno, 4, cnt, null)) Wednesday, [code]......
This gave me thousands of rows as each UNIX time stamp is unique. Is there a way of grouping on UNIX time stamp.
I've tried for pivot query feature of Oracle 11g, but I'm trying for pivot result on multiple column.
Herewith I'm displaying my try on single column pivot query.
SQL> select * from 2 (select deptno,job,sal 3 --,comm 4 from emp) 5 pivot (sum(sal) as payment for job in('CLERK','SALESMAN','MANAGER')) 6 order by 1;
[code]....
I've tried this one also, but it didn't seems to be working.
SQL> select * from 2 (select deptno,job,sal,comm 3 from emp) 4 pivot (sum(sal) as payment_sal,sum(comm) as payment_comm for job in('CLERK','SALESMAN','MANAGER')) 5 order by 1;
I tried to follow Pivot query using XML option in APEX
I can get the data into the xml serialized state, but how do I display the actual values in a region? See my example app below. I want the two far right columns in the Pivot XML region to duplicate what is in the original version.
I just so happen to be the one trail blazing the pivot function for the section of the company I work in. (Needless to say, a Sesame Street style answer will not be offensive.) We are literally in the process of upgrading to 11g (11.2.0.1.0). Sadly, none of our more experienced programmers now anything about the pivot function. Not really surprising to me since we've been working in 10g. Anyway, I am using SQL Developer version 3.0.04 which I know is not the newest but I don't yet have permission to upgrade. I used [URL] to get me as far as I am on this function.
The script I am having problems with is:
SELECT * FROM (SELECT
[Code]....
The error I'm getting is:
ORA-01738: missing IN keyword 01738. 00000 - "missing IN keyword" *Cause: *Action: Error at Line: 16 Column: 2
The error indication bounces between line 15 and 16. If I put IN at the end of 15 I then have a missing right parenthesis error...
We have been designing resource management system and want to provide flexible way to extend resource properties at runtime. So we are storing resource properties in a single table,
e.g. select * from _kvID K V---- ----- ----- 1 name Bob 1 age 30 1 gender male 2 name Susan 2 status married convert to+-----+-------+--------+----------+| key | color | height | whatever |+-----+-------+--------+----------+| 1 | green | 15 | --- || 2 | --- | --- | lol |+-----+-------+--------+----------+example of dynamic pivot Dynamic SQL Pivoting – Stealing Anton’s Thunder</title> //<title>AMIS Technology Blog…
Is it possible to create interactive report with dynamic columns updated when _kv will be changed?Is it possible to create add/edit dynamic form depends on key set if we add value type description?
I need a helo to pivot table with variable columns, I have a pivot table :
SELECT a.*FROM (SELECT codigo_aluno,nome_aluno , id_curso,dia FROM c_frequencia where dia like '201308%') PIVOT (sum(null) FOR dia IN ('20130805' ,'20130812','20130819','20130826'))
a but I need to run the select with values for dia , getting from a other table :
SELECT a.*FROM (SELECT codigo_aluno,nome_aluno , id_curso,dia FROM c_frequencia where dia like '201308%') PIVOT (sum(null) FOR dia IN (select dia from v_dia_mes ))
I have 2 tables , which can be mapped with a common column and the second table's rest of the columns should become like columns(like pivot) and in the second table itself 1 more column called value , this sum of the value becomes a value to the pivot column value. For this I am using CASE structure and I am writing the individual case for each column , but I am sure after some extent case don't work at all and I would like to do it dynamically( when ever the new entry will come into the source table , my proc has to pick automatically the new value and put the new value as part of pivot structure) , so pls share your inputs and if possible provide some sample code.
I have a PIVOT XML query that returns results that I want to display as an HTML report from SQLPlus. Is there a way that this can be done readily? I have searched the net an found references to XML Publisher but in my current situation we do not have the product available.
I'm using pivot query feature of oracle 11g and came across a strange situation where i need to pass a "select statement" in a "in clause" of pivot query.
I have tried with pivot xml but it not giving desired output in sql*plus session.It is giving unreadable output.
select * from (select uin,testing_id,pfa_result from test1) pivot xml (max(pfa_result) as result for (testing_id) in (select distinct testing_id from test1));
[code]....
Here actually i want to use "select distinct id from test1" instead of "in (11,12,13,14,15)". Because i don't know how many id's will be there and of which values. e.g. 11 or 21 or 25.
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit PL/SQL Release 10.2.0.5.0 - Production "CORE10.2.0.5.0Production" TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production
See attachment for table creation and table data.
The table tbl_completed has two columns, id and completed. I have created a query that does provide me with the result set that I need, but I need to pivot the rows into columns. So instead of a result set to look like this:
select completed, count(completed) as theCount from tbl_completed group by completed;
COMPLETED THECOUNT Y 772 N 720
I need a result set to look like this:
COMPLETED,Y,N THECOUNT,772,720
The best solution that I have discovered is this, but I figuring their might be a better way.
select (select 'THECOUNT' from dual) as COMPLETED, (select count(completed) from tbl_completed where completed = 'Y') as Y, (select count(completed) from tbl_completed where completed = 'N') as N from dual;
