SQL & PL/SQL :: Pivot Right Command To Use?

Aug 28, 2011

I have the following table structure:

PK1,PK2,PK3,A1,B1,C1,A2,B2,C2

Which I want represent as follows:

PK1,PK2,PK3,A1,A2
PK1,PK2,PK3,B1,B2
PK1,PK2,PK3,C1,C2

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.

View 4 Replies


ADVERTISEMENT

Brtools Command Versus Oracle Command For Shutdown And Startup (statistics)

Sep 13, 2013

we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?

View 3 Replies View Related

SQL & PL/SQL :: How To Use Pivot With Parameters

Apr 16, 2013

How can you use pivot with a parameter? If you attempt to use a parameter then an ORA-500: bind variable not supported is given

Below is a simplified version of the sql to illustrate teh problem.

create table test_pivot
(
ord_no number,
qty number,
loc varchar2(10)
):
insert into test_pivot values (123, 3, 'LOC1');
insert into test_pivot values (123, 2, 'LOC1');
insert into test_pivot values (234, 4, 'LOC1');
insert into test_pivot values (234, 5, 'LOC1');

-- this one is fine

Select * From
(
Select Ord_No, Qty, Loc
From Test_Pivot
Where Ord_No In ('123', '234')
)
Pivot (Sum(Qty) For Ord_No In ('123', '234') )

-- how to use pivot with parameters

Select * From
(
Select Ord_No, Qty, Loc
From Test_Pivot
Where Ord_No In (:Ord1, :Ord2)
)
Pivot (Sum(Qty) For Ord_No In (:Ord1, :Ord2) )

View 4 Replies View Related

PL/SQL :: Pivot The Table

Sep 24, 2013

pivot the table. Table

dataNAMELINE_TYPE_REF_IDClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserskkondNode NamePB15CKYNum Contacts3ClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserdrcoulNode NameC4F6JJ1Num Contacts21ClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserdrcoulNode NameC4F6JJ1Num

[Code]....

View 4 Replies View Related

PL/SQL :: SQL Pivot Dates

Jan 28, 2013

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.

View 7 Replies View Related

PL/SQL :: Pivot Table Sql

Apr 18, 2013

i have a table which looks like this

TRANS_ID TRANS_TYPE CONNECTION_DATE CLOSED_DATE
LK490 4 01-01-2009 01-01-2011
LK490 3 01-01-2010 01-01-2012

SELECT TRANS_ID, TRANS_TYPE, CONNECTION_DATE , CLOSED_DATE FROM TABLE_A

and want to pivot it like this

TRANS_ID 4_CONNECTION_DATE 4_ CLOSED_DATE 3_CONNECTION_DATE 3_ CLOSED_DATE
LK490 01-01-2009 01-01-2011 01-01-2010 01-01-2012

View 3 Replies View Related

Rows Into Columns Like Pivot

Sep 5, 2011

I have this following data in a single table

Student
Semester
Subject
Marks

and I want to achieve the following.

I am asked to write a query with the students as Rows with thier subjects and marks as per thier semester which is the columns.

new to this type of queries...This is somewhat like pivot..

Those who have not appeared for a semester should be null just exactly as shown above.Is it posible ?

View 5 Replies View Related

Pivot Query In Oracle

Jan 24, 2011

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

View 3 Replies View Related

Any Way To Pivot Results Of A Query?

Apr 23, 2007

Is there anyway to pivot the results of a query?

so if i have:

SELECT GROUP, count(*)
FROM GROUP
GROUP BY GROUP

And it give the following output:

A 10
B 50
c 24

Is there anyway to put into this format?

A B C
10 50 24

I am doubting that there is and that i am going to have to handle this in my code later, but it never hurts to ask!

View 1 Replies View Related

SQL & PL/SQL :: Pivot Output In Equijoin

Feb 11, 2013

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?

View 2 Replies View Related

SQL & PL/SQL :: Transpose (Not Pivot) A Table

Aug 18, 2010

I have a table in the following format:

CTitle1, CTitle2, CTitle3, CTitle 4, CTitle5
Row1Val1,Row1Val2,Row1Val3,Row1Val4,Row1Val5
Row2Val1,Row2Val2,Row2Val3,Row2Val4,Row2Val5
Row3Val1,Row3Val2,Row3Val3,Row3Val4,Row3Val5
Row4Val1,Row4Val2,Row4Val3,Row4Val4,Row4Val5
Row5Val1,Row5Val2,Row5Val3,Row5Val4,Row5Val5

I have an application that requires the data to be in the following format:

CTitle1, Row1Val1,Row2,Val1,Row3Val1,Row4Val1,Row5Val1
CTitle2, Row1Val2,Row2,Val2,Row3Val2,Row4Val2,Row5Val2
CTitle3, Row1Val3,Row2,Val3,Row3Val3,Row4Val3,Row5Val3
CTitle4, Row1Val4,Row2,Val4,Row3Val4,Row4Val4,Row5Val4
CTitle5, Row1Val5,Row2,Val5,Row3Val5,Row4Val5,Row5Val5

So I am truly trying to transpose the columns and rows and not doing a traditional pivot. How I can do this in Oracle 9i?

View 9 Replies View Related

SQL & PL/SQL :: Report Using Pivot Table

Jul 19, 2013

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

Example input data
----------------------------------------------------------------
|CUST | PRODSEQ | STARTDAT | ENDDAT |ATTRID |ATTRVALUE |
----------------------------------------------------------------
|C1 | 1 | 1/1/2013 | 1/15/2013 | 1 | 1 |
|C1 | 1 | 1/1/2013 | 1/10/2013 | 2 | A |
|C1 | 1 | 1/1/2013 | | 3 | X |
|C1 | 1 | 1/1/2013 | | 4 | G |
|C1 | 1 | 1/11/2013 | 1/31/2013 | 2 | B |
|C1 | 1 | 1/16/2013 | 1/20/2013 | 1 | 2 |
|C1 | 1 | 1/21/2013 | 1/31/2013 | 1 | 3 |
----------------------------------------------------------------

Expected output
----------------------------------------------------------------------------------------
|CUST | PRODSEQ | STARTDAT | ENDDAT | ATTR1VAL | ATTR2VAL | ATTR3VAL | ATTR4VAL|
----------------------------------------------------------------------------------------
| 1 | 1 | 01-JAN-2013 | 10-01-2013 | 1 | A | X | G |
| 1 | 1 | 11-JAN-2013 | 15-01-2013 | 1 | B | X | G |
| 1 | 1 | 16-JAN-2013 | 20-01-2013 | 2 | B | X | G |
| 1 | 1 | 21-JAN-2013 | 31-01-2013 | 3 | B | X | G |
----------------------------------------------------------------------------------------

View 1 Replies View Related

SQL & PL/SQL :: Oracle Pivot Or Other Ways?

Dec 6, 2010

This is how data is in table.

SQL> SELECT order_num, jcds.change_num, jcds.object_status, jcds.time_changed
2 FROM tjcds_f jcds
3 WHERE change_num = '001'
4 AND object_status IN ('E0001', 'I0002', 'I0009', 'I0028')
5 and order_num='000001000001';

ORDER_NUM CHANGE_NU OBJECT_STAT TIME_CHANGED
------------ --------- ----------- --------------------------------------------
000001000001 001 I0002 01-NOV-10 02.52.56.000000 PM
000001000001 001 I0009 01-NOV-10 09.30.20.000000 AM
000001000001 001 I0028 01-NOV-10 10.36.32.000000 AM

Needed Output:
ORDER_NUM CHANGE_NU E0001 I0002 I0009 I0028
------------ --------- ----------- ------------------------------- ------------------------------ ---------------------------------
000001000001 001 01-NOV-10 02.52.56.000000 PM 01-NOV-10 09.30.20.000000 AM 01-NOV-10 10.36.32.000000 AM

My Query:
SELECT order_num, jcds.change_num,
decode(jcds.object_status,'E0001', jcds.time_changed) E0001 ,
decode(jcds.object_status,'I0002', jcds.time_changed) I0002,
decode(jcds.object_status,'I0009', jcds.time_changed) I0009,

[code]...

how do i get the above requirement in one row? is it possible.

View 5 Replies View Related

PL/SQL :: Pivot / Unpivot Data?

Mar 20, 2013

I am not sure if we can do this as i tried many solution for pivoting/unpivot My data looks like from table when i do Select * from table_name

col1                       col2                 col3      col4  col5 col6    col7 col8
20130320_0938      C11               1416      98    93   30    30    32                                    
20130320_0938      C22                   26      92    73   50    50    65                                    
20130320_0938      C33                    86      95    81   63    63    76

I want the data to look like

[code]...

View 5 Replies View Related

PL/SQL :: Get Expected Results From Pivot?

Jan 31, 2013

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

[code]...

and am getting output as

param_type      eff_date_param_value   disc_date_param_value   initial_ignoring_param_value
mbn256_type    <null>                                 <null>                           <null>

View 2 Replies View Related

SQL & PL/SQL :: Pivot Table Using UNIX Timestamp

Feb 23, 2010

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.

select
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 code produces the result:

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.

View 4 Replies View Related

SQL & PL/SQL :: Pivot Table Comparison Between Months

Jun 28, 2010

SQL> select * from query;

CUSTO SWITCH DATE_X METRIC COUNT_X SEQUENCE_NO FILE
------- ------- --------- ------- ---------- ----------- ----
JCI S1 28-JUN-10 PORT1 10 4 TNB
JCI S1 28-JUN-10 PORT2 4 4 TNB
JCI S1 28-JUN-10 PORT3 8 4 TNB
JCI S2 28-JUN-10 PORT1 1 2 TNB
JCI S2 28-JUN-10 PORT2 5 2 TNB
JCI S3 28-JUN-10 PORT1 6 8 TNB
JCI S5 28-JUN-10 PORT2 2 4 TNB

------->s5 only in the current month-it was not in the previous month data collection
-----------------------------------------------------------------------------
JCI S1 28-MAY-10 PORT1 10 3 TNB
JCI S1 28-MAY-10 PORT3 5 3 TNB
JCI S2 28-MAY-10 PORT1 4 1 TNB
JCI S2 28-MAY-10 PORT2 2 1 TNB
JCI S2 28-MAY-10 PORT3 8 1 TNB
JCI S3 28-MAY-10 PORT2 7 7 TNB
JCI S3 28-MAY-10 PORT3 5 7 TNB
JCI S4 28-MAY-10 PORT1 2 10 TNB
------->s4 in the previous month data

15 rows selected.

1 SELECT A.CUSTO,A.SWITCH,
2 A.PORT1-B.PORT1,
3 A.PORT2-B.PORT2,
4 A.PORT3-B.PORT3
5 FROM (SELECT
6 A.CUSTO

[Code] ......

CUSTO SWITCH A.PORT1-B.PORT1 A.PORT2-B.PORT2 A.PORT3-B.PORT3
------- ------- --------------- --------------- ---------------
JCI S1 0 4 3
JCI S2 -3 3 -8
JCI S3 6 -7 -5

How to include the Switch S4 and S5 in the output. how implemented FULL OUTER JOIN

View 13 Replies View Related

SQL & PL/SQL :: Multiple Column In Pivot Query?

Dec 17, 2011

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;

[code]....

I want result like below.

DEPTNO PAYMENT 'CLERK'_PAYMENT 'SALESMAN'_PAYMENT 'MANAGER'_PAYMENT
---------- ------- --------------- ------------------ -----------------
10 SAL1300 2450
20 SAL1900 2975
30 SAL 950 5600 2850
30 COMM 2200

is it possible to have multiple column pivot query.

View 2 Replies View Related

SQL & PL/SQL :: Access Rights To Be Given For Using Pivot Function

Dec 28, 2011

Is any access rights to be given for using pivot function...i tried below query but it throws error

select * from BI_BALNCE_SHEET
PIVOT(sum(balance) for gl in('Income','Expenditure'));

View 8 Replies View Related

Application Express :: Pivot XML In Region

Jul 12, 2012

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.

[URL].......

View 3 Replies View Related

PL/SQL :: Pivot In 11g Producing Keyword Error?

Dec 19, 2012

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

View 6 Replies View Related

Application Express :: IR With Dynamic Pivot?

Oct 9, 2013

 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 &#8211; Stealing Anton&#8217;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? 

View 5 Replies View Related

PL/SQL :: Pivot Table With Variables Columns

Aug 27, 2013

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 ))

View 3 Replies View Related

Dynamic Pivot Table (Partition) Creation

Oct 31, 2010

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.

View 3 Replies View Related

PIVOT XML Results To HTML Table In SQLPlus?

Jul 27, 2012

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.

View 4 Replies View Related

SQL & PL/SQL :: How To Use (select Statement) Rather Than (in Clause) In Pivot Query

May 27, 2011

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.

SQL> CREATE TABLE TEST1
2 (
3 UIN NUMBER(8) NOT NULL,
4 TESTING_ID NUMBER(4),
5 PFA_RESULT VARCHAR2(30 BYTE)
6 );

[code]....

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.

View 3 Replies View Related

SQL & PL/SQL :: How To Pivot Table So That Rows Become Column Names

May 9, 2012

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;

COMPLETED,Y,N
THECOUNT,772,720

View 3 Replies View Related

JDeveloper, Java & XML :: Flatten Output From (pivot Xml)?

Jan 18, 2011

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,

View 13 Replies View Related

Forms :: Pivot Table Inside Group

Mar 21, 2011

I want to use the pivot table inside the group.

View 3 Replies View Related

SQL & PL/SQL :: Show Result Of Query Just Like Pivot Table?

Jul 20, 2011

me in building a query. I want to show the result of the query just like pivot table.
Test case
CREATE TABLE CPF_YEAR_PAYCODE
(
CPF_NO NUMBER(5),
INC_DATE DATE,
PAYCODE_TYPE CHAR(1 BYTE),

[code]...

I want that my query should look like the format as attached in the xls sheet.

View 1 Replies View Related







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