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
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?
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]....
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
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.
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
I am trying to create a simple parametised procedure when all I am doing is dynamically inserting data from a set of tables and cols on a where clause.
And for the result set I am also inserting these input variables for future ref.
----------------------------------
CREATE OR REPLACE PROCEDURE DATA_TEST (IN_TABLE_NAME IN VARCHAR2, IN_ATTRIBUTE IN VARCHAR2 )
iS
FirstClause VARCHAR2(3000) := 'INSERT INTO ABC (
COL1, COL2, COL3,COL4,COL5, COL6) ';
selectClause VARCHAR2(3000) :=
[Code]...
Now, the problem is that the proc is not compiling, because of that escape character.
Is there anything wrong that I am doing or some other settings needs to be done?
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?
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?
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.....
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.
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
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.
The Table having 3 columns
col0col1 , col2
P112
P112
P102
P113
P115
I want to retrieve the changes rows only.
Output like
col0col1 , col2
P112
P102
P113
P115
in my sql we can use limit to select first 4 rows in the table then next 4 rows ,can oracle do that ?
I have fifty rows inside the table
select * from student where rownum between 0 and 4 order by stuid ->it work
select * from student where rownum between 5 and 9 order by stuid -> did not work
what is the correct way to do it ?
drop table test
/
create table test
(
lib varchar2(100)
)
/
insert into test values ('111/aaa/bbb/ccc');
insert into test values ('222/aaa/bbb/ccc');
insert into test values ('333+444/aaa/bbb/ccc');
insert into test values ('333/aaa/bbb/ccc');
insert into test values ('222+333+444/aaa/bbb/ccc');
insert into test values ('222+333+444+555/aaa/bbb/ccc');
I want to get this :
lib count
--------------- -----
111/aaa/bbb/ccc 1
222/aaa/bbb/ccc 3
333/aaa/bbb/ccc 4
444/aaa/bbb/ccc 3
555/aaa/bbb/ccc 1
I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.
View 4 Replies View RelatedWe are using PL/SQL Release 11.2.0.2 .I would like to pull a query with each student each day an attendance record.Our database setup an AM and PM Period for all elementary students. I will pull if they absent both periods(AM, PM), then count that as one day absent.The hard part is I need to put the AM absent code and PM absent code - which is basically to put two records for each student's AM and PM absent code into one row.
Below is the query I use, but it violates the key of database, for PK is studentid+ attendance date. My query result turns out for some students they have different attendance code in AM vs PM, there are two records returned.
SELECT DISTINCT TO_CHAR(S.Student_Number) ,
TO_CHAR(Am.Schoolid) ,
Sps_School_Year('C') ,
TO_CHAR(Am.Att_Date,'MM/DD/YYYY') ,
(
[code]....
We have large (millions of records) Slow changind dimension (SCD) type 2 (see "Creating another dimension record " URL>.....We need to get several rows from this SCD for each key (AGREEMENT_ID) in a SQL query - to join to facts table and get several data points of each agreement (on several different points in time) stored in SCD.Here is SCD table structure:
CREATE TABLE AGREEMENT
(
"AGREEMENT_ID" NUMBER(*,0) NOT NULL ENABLE,
"ACTUAL_DATE" DATE NOT NULL ENABLE,
"ACTUAL_END_DATE" DATE NOT NULL ENABLE,
"OPEN_DATE" DATE NOT NULL ENABLE,
"LIMIT" NUMBER(23,8)
--++ a lot of other fields not needed for this task ....
CONSTRAINT "PK_MD_AGREEMENT" PRIMARY KEY ("AGREEMENT_ID", "ACTUAL_DATE") USING INDEX
)
The 1st simple approach would be to join facts to SCD as many (N) times as many different points of time you need - resulting in N Full Table Scans for SCD:
select ...
from fact,
AGREEMENT agr1,
AGREEMENT agr2,
AGREEMENT agr3
where
fact.AGREEMENT_ID = agr1.AGREEMENT_ID and agr1.open_date between actual_date and actual_end_date
and fact.AGREEMENT_ID = agr2.AGREEMENT_ID and :dateBOP between actual_date and actual_end_date
and fact.AGREEMENT_ID = agr3.AGREEMENT_ID and :dateEOP between actual_date and actual_end_date
2nd approach: 1 Full Table Scan for SCD + group by:
select ...
from fact,
(
Select AGREEMENT_ID,
max(case when open_date between actual_date and actual_end_date then LIMIT end) LIMIT_At_Open_DATE,
max(case when :dateBOP between actual_date and actual_end_date then LIMIT end) LIMIT_At_BeginOfPeriod_DATE,
max(case when :dateEOP between actual_date and actual_end_date then LIMIT end) LIMIT_At_EndOfPeriod_DATE
from agreement
-- ++optionally WHERE for those 3 dates, but possibly with no effect on non-partitioned table? Or WHERE to put less data on MAX() input (3 row for each agreement instead of 4...1000 without WHERE?)
group by AGREEMENT_ID
) agr
where fact.AGREEMENT_ID = agr.AGREEMENT_ID
Simple question, Why comparison operator ANY returns FALSE if no rows returned, and why operator ALL return TRUE if no rows returned? I dont know is this some kind of language or math assumption or is this just oracle rule?
View 18 Replies View Relatedi have 2 tables
A B
id a.id
name
type
There are at most 2 entries of a in b. Depending on the value of the type column in B, this determines whether the entry should be male or female. I want to have a select statement that will retrieve 2 rows into one row essentially like below, how is this done:
id male_name female_name
1 paul paula
the column names will appear as such, if its a 0 its a male name if its 1 its a female name, there will generally be 2 entries in B for 1 value of a.
I would want to know how can we predict how many rows are fetched per second for a particular query. What are the factors which are responsible for this.hoe does this whole process of fetching records from Source happens. Like when a query is fired how does it try to access the table and fetch records and how what are the factors whichc are responsible for this and how can we predict how many rows can be fetched per sec
View 8 Replies View RelatedThere are many reduplicate rows in a big table,what is the best way to get the reduplicate rows?
View 5 Replies View RelatedI have following table.
CREATE TABLE ORAFAQ
(
DPT NUMBER,
EMP NUMBER,
SAL NUMBER
)
[Code]....
DPTEMPSAL
1110
12100
131000
241500
25100
26500
27100
but i need data like
Dept
1
1 2 3 --3 columns show each employee
10 100 1000 --3 columns show salary below each employee
2
4 5 6 7
1500 100 500 100
How can we commit for every 500 rows in pl/sql block.
begin
for i in 1 to 10000
loop
Insert into t1 values(i);
commit;
end loop;
end;
Here I am commiting after all the rows are inserted ,but i want to commit for every 500 rows are inserted .
i want to all rows value into single rows.
for example;
Input data:
Sec_SSC_ID Column_nameAs of dateOld valu New Value
IBM Mat_dt 10/10/20101/1/2001 1/1/2002
IBM Bid Market 10/10/201075 85
IBM asset_nm 1/1/2011International IBM
MSFT asset_nm 1/2/2011Microsoft Intel
MSFT Bid Market price 1/1/201189 90
Output data
I have attached sample output file with it.
how to get this via sql?
Scenario like this , I have a dB which contain 100 tables, i want to find out each table contain how many rows excluding
system tables ..
how can i write query for this.
tried searching google and this site too, found postings on WM_CONCAT, STRAGG, concat_all, LISTAGG functions by Michel and have experimented with these, but either the syntax is giving me a hard time or i just have not got the concept down.
Trying to get 2 rows into one. Have provided the create statements and insert of data. Also below will show what is returned with a Select i have and what is ideally required.
CREATE TABLE Person_Lang
(
Person_ID NUMBER NOT NULL,
Language_ID NUMBER NOT NULL,
Contact_Name VARCHAR2(255 CHAR),
Main_Phone VARCHAR2(255 CHAR),
Secondary_Phone VARCHAR2(255 CHAR),
[Code]...
Data Returned from Select is:
1 46 905-231-3319 22 Street11 Apt402 Brantford
1 46 905-231-3319 23 Street12 Apt315 MainTown
Ideally what is required back is:
1 46 905-231-3319 22 Street11 Apt402 Brantford 23 Street12 Apt315 MainTown
i want to sum different rows and want get wum in nest column.
for example i have fee column and there is some different student fee in specific month for example(jan).i want to sum this fee against the month of Jan.
How to get the previous value of row with calling function to add value in SELECT statement for the row value.
Consider the example Table A1 having column a with values 1,NULL,NULL,NULL
SELECT CASE WHEN a IS NULL THEN (prev_row_value+function_return_Value) ELSE a END as A from A1
And my result-set should be like
a
----------------------
1
1+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Below is sample code but doesn't fulfill my criteria
[code]....
Output is
A A2
---------------------------
1 1
3
3
3