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


ADVERTISEMENT

SQL & PL/SQL :: Kind Of Transpose Table

Sep 1, 2011

My need is to build columns for each month in table and find changes between monthes. My first try works fine, but next step should be performed using wider period.

with t2 as (
select date '2008-02-29' as arcdate, 1867763 as cid, 2 as dealtypeid, 840 as currencyid, 3649509 as dealid, 7.5 as avalue from dual union all
select date '2008-02-29' as arcdate, 1867763 as cid, 2 as dealtypeid, 826 as currencyid, 3611270 as dealid, 12 as avalue from dual union all
select date '2008-02-29' as arcdate, 1867763 as cid, 3 as dealtypeid, 826 as currencyid, 3735006 as dealid, 12 as avalue from dual union all
select date '2008-01-31' as arcdate, 1867763 as cid, 3 as dealtypeid, 826 as currencyid, 3735006 as dealid, 13.5 as avalue from dual union all
select date '2008-01-31' as arcdate, 1867763 as cid, 2 as dealtypeid, 826 as currencyid, 3611270 as dealid, 13 as avalue from dual union all
[code]....

As you can see, I've got virtual table named jan2008, feb2008, mar2008, apr2008 left-joined to tt1 table. But what should I do with much more wider period, for example 5 years? Can I do it without creating virtual tables for each month?

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

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

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

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

Application Express :: Pivot Table Layout?

Nov 5, 2012

in my application i have a requirement as follows

1) column 1 indicator
2) column 2 important.

and the other columns are target actual and current year, previous year and next year.the layout should be in pivot table lay out as follows here the three columns will be saved dynamically as 2012,2011,2013 and the respecitive percentage in target and actual column

current_year previous_year next_year
target 10% 12% 5%
actual 5% 8% 5%

View 1 Replies View Related

SQL & PL/SQL :: Transpose Function In Link

Jul 5, 2010

my table is like this.

id,ordnum,User
1 ON1 Abc
2 ON2 Bcd
3 ON3 Xyz
4 ON4 Bcd
5 ON5 Abc
6 ON6 Pqr

I want to write select query to get result in below format:

User Ordnum
Abc ON1,ON5
Bcd ON2,ON4
Xyz ON3
Pqr ON6

I am using Oracle 8i, I tried transpose function in the link URL.....

View 24 Replies View Related

SQL & PL/SQL :: Transpose Data And Display It

Jul 1, 2011

I have to transpose data and display it using Sql. How can I do that?

Here is how the data looks in the table now:

Deal Cashflow Date
---------------------------
0007 1228888 01/12/2011
0007 898998 02/12/2011
0007 999999 03/12/2011
0008 888888 01/12/2011
0008 777777 02/12/2011

When I transpose the data, it should look like this:

Deal 01/12/2011 02/12/2011 03/12/2011
0007 1228888 898998 999999
0008 888888 777777

Never had to do this in the past.

View 6 Replies View Related

PL/SQL :: How To Transpose Cols To Rows

Aug 6, 2013

I have a requirement to transpose the column to rows like the one below, i am not getting the logic on how to do it, If any of you have come across this logic,

Existing Table:ABCD1234
Hypothetical table: ColumnValueA1B2C3D4

View 2 Replies View Related

SQL & PL/SQL :: Query To Transpose Column Names To Row

Mar 9, 2011

Query : select * from scott.dept where deptno=10;

Result
DEPTNO |DNAME |LOCATION
-------|----------|----------
10 |ACCOUNTING|NEW YORK

But i want the above resultset as below,

COL1 COL2
----------------------
DEPTNO | 10
DNAME | ACCOUNTING
LOCATION | NEW YORK

View 4 Replies View Related

SQL & PL/SQL :: Transpose Dynamic Rows To Columns

Jul 15, 2013

I have a result table from a select query. Create_table.txt

FDAVAILABILITY_2GAVAILABILITY_3GTCH_TRAFFICHOSRCSSR
2013 Jun0.9870.992928359.09430.98360.975
2013 W270.99210.994328512.48650.98320.9756
2013 W280.99320.993728659.32560.98430.9734
2013 W290.99530.993328355.02630.98480.9741

I need to transpose this table to below one. But since my rows in 1 table is dynamic and it will increment each week and month I couldn't get a correct result from unpivot.

2013 Jun2013 W272013 W282013 W29
AVAILABILITY_2G0.99 0.99 0.99 1.00
AVAILABILITY_3G0.99 0.99 0.99 0.99
TCH_TRAFFIC28359.094328512.486528659.325628355.0263
HOSR 0.9836 0.9832 0.9843 0.9848
CSSR 0.975 0.9756 0.9734 0.9741

Is it possible to create above table from attached , with a select statement?

View 7 Replies View Related

SQL & PL/SQL :: Transpose And Dynamic Column Depending On Result

Nov 4, 2013

I am having a table structure like below.

city amt1 tx_date
-----------------------------------------------------
Blr 10000 20050101
Delhi 25000 20050101
Blr 10000 20050102
Blr 2100 20050103
DELHI ...... 20050104
...... ....... ........

i have to place the data in following manner.

city 20050101 20050102 20050103 20050104 ...........etc
-------------------------------------------------------
Blr 10000 10000 2100
Delhi 25000 0 0

Depending on the no. of distinct dates in table 1 i have to make those many columns in table 2. I m trying to write a Query in SQL. If its not possible in SQL give me PL/SQL procedure.

View 6 Replies View Related

PL/SQL :: Conditional Transpose Of Column Data Into Rows

Jul 26, 2013

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production versionI have following table - 
drop table t2;

create table t2(BATot,Ly_BATot,LLy_BATot,BScTot,Ly_BScTot,LLy_BScTot,BAMSTot,Ly_BAMSTot,LLy_BAMSTot) as select
5000,2000,12600,20000,45600,35000,45000,56000,65000 from dual ;

select null class, batot,ly_batot,lly_batot,bsctot,ly_bsctot,lly_bsctot,bamstot,ly_bamstot,lly_bamstot from t2; Simple DML I am using -
SELECT * FROM T2;

C      BATOT   LY_BATOT  LLY_BATOT     BSCTOT  LY_BSCTOT LLY_BSCTOT    BAMSTOT LY_BAMSTOT LLY_BAMSTO
- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        5000       2000      12600      20000      45600      35000      45000      56000       65000

[Code]....

View 7 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

SQL & PL/SQL :: Query To Transpose Quest_id And Ans_id Columns Into Rows For Matching Records

Nov 16, 2012

create table ref_tbl (col1 varchar2(10),
col2 varchar2(10),
QUEST_ID_1 varchar2(10),
ANS_ID_1 varchar2(10),
QUEST_ID_2 varchar2(10),
ANS_ID_2 varchar2(10)
);
[code]...

Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records...my output should be like

CUS_IDCOL1COL2QUEST_IDANS_ID
------------------------------------------------
1PMP1234Q101A1234
1PMP1234Q102A2345
1STR2345Q201A2234
2PMP1234Q101A1234
2PMP1234Q102A2345
2STR2345Q201A2234

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

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

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







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