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;
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,
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.
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.
I want to write a sql qeuery to get result similar to expected average column as shown in sheet.
Record can be uniquely identified by columns ID, PRODUCT and OFFICE. I want to calculate average for each date considering the same record does not exists earlier i.e. I want to consider the latest vote while calculating average. E.g.
Average till date 1/1/2011 is Avg (2, 4, 3) = 3 (as no repeating vote value exists) Average till date 2/1/2011 is Avg (4, 3, 5, 3) = 3.75 (excluding vote value 2 as 122_UK_LONDON was provided his vote earlier, so considered latest vote value i.e. 5) Average till date 3/1/2011 is Avg (3, 5, 3, 6, 5, 8 ) = 5 (excluding vote value 2 for 122_UK_LONDON and vote value 4 for 967_Europe_London)
I'm using the Oracle Emp,Dept tables as my sample. I want to display certain table column values based on some criteria. If met, display those values otherwise display other column values
For example:
So when dept.deptno=10, I want to display these 2 columns values 1. dept.deptno 2. dept.dname
otherwise, display these 2 columns values 1. dept.loc 2. null
Can this be done with one case, decode or "other" type of structure going thru the table one time??
SELECT emp.empno, emp.ename, CASE WHEN dept.deptno = 10 THEN to_char(dept.deptno) [code].......
I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query
MONTH_YEAR RMS RMS_OCC TTL_RMS --------------------------------------- SEPTEMBER 200917790017790 OCTOBER 2009183831278818347 NOVEMBER 2009177901460517762
and I need to display this as
COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009 -------------------------------------------------------------- RMS 17790 18383 17790 RMS_OCC 0 12788 14605 TTL_RMS 17790 18347 17762
Currently I have a requirement where I need return data of different columns in rows.
For example: I have a table that contains monthly data for voice calls, sms count and mms count for each mobile. I will need to get the output as summary of voice calls, sms and mms counts in different rows. For this I am using an approach which I am not totally satisfied even though I am getting the required results (majorly because i am querying the table thrice for getting this data).
I would want to know about any alternate ways of implementation.
insert into UsageData values ('9999','JAN',1,2,3); insert into UsageData values ('9888','JAN',5,20,1); insert into UsageData values ('9777','JAN',4,5,9); insert into UsageData values ('9666','JAN',200,111,8); insert into UsageData values ('9555','JAN',154,1534,3); insert into UsageData values ('9444','JAN',0,2,212);
--Query
select 'VOICE_CALL' AS EVENT,sum(VOICE_CALL) AS UNITS from UsageData where cal_month ='JAN' union all select 'SMS',sum(SMS) from UsageData where cal_month ='JAN' union all select 'MMS',sum(MMS) from UsageData where cal_month ='JAN';
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?
There are millions of DISTINCT ID values in TABLE_1 and corresponding to each ID there are some values for all the days of a month. I need to insert these values in TABLE_2 in the above format.
Calculating the difference between the two rows of same columns? Example of my Scenario is as follows
I have a table as shown below, there are checks thats has been issued per week (to tell how its been issued per week ,if you see the week number ,there is a change in week no for period of 7 days i.e. what i have to show on my results tables based on week no and probably we may need to take into account for check no too.)
result table and date range i picked up was 1/1/2012-1/31/2012
wekno ! checkno ! end date ! previous period ! Daysin period ------ ! ------ ! ------ ! ------------- ! ------------ 1 ! 1 ! 1/7/2012 ! null (as previous period is 2011 and so prvs year i dont count) ! 7 this because we see change in wkno per 7 days 1 ! 2 ! 1/8/2012 !null becoz this one falls same week and therefore previous peroid would be the same as above ! 7 above reason 1 ! 3 ! 1/9/2012 ! null becoz this one falls same week and therefore previous peroid would be the same as above ! 7 above reason 2 ! 1 ! 1/14/2012 ! 1/7/2012 becoz this a second week as you see the change in wekno and therefore its previosu period is 1/7/2012 ! 7 above reason 2 ! 2 ! 1/15/2012 ! 1/7/2012 becoz this check falls on the same second week so previosu would be 1/7/2012 ! 7 above reason 3 ! 1 ! 1/21/2012 ! 1/14/2012 as a new week no starts this gonn be 1/4/2012 ! 7 above reason
I have a table with columns emp_i, LOC_C and SUBSID_C. I want to find all emp_i's with LOC_C OR SUBSID_C as always NULL. Please note that the value should be NULL, always, for all dates.
The query --- should return 102 as LOC_C OR SUBSID_C is ALWAYS NULL. should return 103 as LOC_C is ALWAYS NULL. should not return 101, as LOC_C is not ALWAYS NULL. In other words, the query should give list of emp_i who never ever had a non-null value for LOC_C OR SUBSID_C. The purpose is to find the emp_i for which the columns LOC_C and SUBSID_C are never used.
I tried the query: --------------------------------------------------- SELECT DISTINCT ORG_EMP_I FROM tab1 WHERE ORG_GRP_I = 58 AND ORG_EMP_I NOT IN ( SELECT DISTINCT org_emp_i FROM tab1 ap WHERE ap.ORG_GRP_I = 58 AND trim(ap.LOC_C) IS NOT NULL OR ap.ORG_SBSID_C IS NOT NULL ) ---------------------------------------------------
SELECT a.objname, c.property1, c.value1 FROM datatable a, datatabledet c WHERE a.OBID=c.DATAOBID and a.CLASSNAME='Class1'; OBJNAME PROPERTY1 VALUE1 280-419-1994psCls1Attr3Attr1Value3 280-419-1994psCls1Attr4Attr1Value4 280-419-1994psCls1Attr5Attr1Value5
[code]....
After query output we put through front end code to make it in the following way i.e. convert rows into columns but with respective data value:
Employee Number Name Value Rate Type Type Assignment Id ----------------------------------------------------------------- 4 Employee 1 800 N 5 Yr Bonus 64 4 Employee 1 1063 N 6 Months Bouns 64 4 Employee 1 24.04 H Reg Hourly Sal 64 5 Employee 2 6923.08 S Reg Sal 65 6 Employee 3 5961.54 S Reg Sal 66 6 Employee 3 15000 N 6 Months Bouns 66
I want to convert above out to look like following
Emp Num Name Value Rate Type Type Val 1 Rate Type 1 Type 1 Value 2 Rate Type 2 Type 2 Assignt Id ------------------------------------------------------------------------------------------------------------------- 4 Employee 1 24.04 H Reg Hourly Sal 800 N 5 Yr Bon 1063 N 6 Mon Bon 64 5 Employee 2 6923.08 S Reg Sal 65 6 Employee 3 5961.54 S Reg Sal 15000 N 6 MontBon 66
Example of Query Output 2.
Employee Number Name Value Rate Type Type Assignment Id -------------------------------------------------------------------------------- 4 Employee 1 800 N 5 Yr Bonus 64 4 Employee 1 1063 N 6 Months Bouns 64 4 Employee 1 1345 N Patent Bonus 64 4 Employee 1 24.04 H Reg Hourly Sal 64 5 Employee 2 6923.08 S Reg Sal 65 6 Employee 3 5961.54 S Reg Sal 66 6 Employee 3 15000 N 6 Months Bouns 66
Desired Output from Above Query. As you can see column have increased as one more row for same employee has increased
ENu Nm Val RTy Ty Val1 RTy1 Ty1 Val2 RTy2 Ty2 Val3 RTy3 Ty3 AsgId ------------------------------------------------------------------------------------------------------------------- 4 E1 24.04 H Reg Hour Sal 1063 N 6 Mon Bns 1345 N Pat Bon 800 N 5YB 64 5 E2 6923.08 S Reg Sal 65 6 E3 5961.54 S Reg Sal 15000 N 6 Mos Bns 66
I huge table with million of rows and no of column more than 300.. is it possible to keep this table in some memory where oracle can access it fast as compare to disk memory.
