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


ADVERTISEMENT

SQL & PL/SQL :: Transpose Rows-to-columns And Columns-to-rows

Oct 6, 2010

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

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

Convert Rows To Columns In A Table?

Apr 24, 2012

How do I convert rows in oracle table to columns by writing a query?

Here is my table.

EID QNo Qual YOP
101 1 B.Tech 2004
101 2 M.Tech 2006
102 1 B.Tech 2003
102 2 M.Tech 2005
102 3 MBA 2007

Now I want the above table in the following format......

EID Qual1 YOP1 Qual2 Yop2 Qual3 Yop3
101 B. Tech 2004 M.Tech 2006
102 B. Tech 2003 M.Tech 2005 MBA 2007

I have maximum of 8 Qualifications, how to achieve this in oracle.

View 3 Replies View Related

Displaying Rows Into Columns In A Table

Oct 20, 2012

I have table having below records.

empno ename deptno
101 a 10
102 b 20
103 c 10
104 d 20
105 e 30

Normal Output

deptno count(*)
-----------------
10 2
20 2
30 1

I want to display like this(rows into columns)
--------------------------------------------------------
Required Output
-------------
10 20 30
2 2 1

View 1 Replies View Related

Converting Rows Into Columns And Updating A Table?

Apr 26, 2012

I have a table A, whose table structure is in the below format.

Table A

ID DESC VALUE
123 A 454
123 B 1111
123 C 111
123 D 222
124 A 123
124 B 1
124 C 111
124 D 44

Now i need to insert the data from this table to another table B, the sturcture of which is as below

Table B

ID A B C D
1234541111111222
124123111144

How do i frame a query to fetch data from table A and insert that into table B? I don't want to use max and decode combination. as it would return only single row for an ID. I need all the id's to be displayed.

View 1 Replies View Related

SQL & PL/SQL :: Comparing Multiple Columns According To Rows From Same Table

Aug 25, 2011

I am new to oracle, I have request to build a query,

we have table that generates data from 7am to 20pm for eavery hour it generates 4 rows and has 43 session values as 43 columns.

Now i want to find for every hour which is the hights session value at what time. in one hour it runs four times like 7, 7:15, 7:30 and 7:45 and each row has date, time and 43 session columns in table...

View 12 Replies View Related

SQL & PL/SQL :: Update Number Of Rows In Three Columns In Table?

Dec 13, 2011

how to update the middle of plenty rows in the middle of the columns

sample_data

id name state REGION LOC
1 v A.p 1 1
2 a
3 g K.A 0 3
4 y
5 i T.N 1 0
6 l M.P 0 1
7 c U.P

This is sample data,and i have this kind of large data and i need to fill the rows which are empty. In three columns state,region,loc with data like 0,web_intimation,1,

View 8 Replies View Related

How To Convert Table Data From Rows To Columns

Mar 11, 2013

I have a Employee table of the following format:

Emp_id | Emp_name | Salary
101 | James | 1000
102 | Julia     | 200

I have to convert or transpose the table data as follows using a SQL statement/function -

Emp_id | 101     |     102
Emp_name | James |     Julia
Salary     | 1000 |     2000
     
How do I achieve this?

View 4 Replies View Related

PL/SQL :: How To Convert Rows To Columns Data In A Table

Dec 5, 2012

I have the situation like i want display the data from table which is storing in rows into a columns....

My table structure will be like this(Here i am taking only one part but have N no. of parts in my system)

Partno Purchase date Qty
111 02-DEC-2012 15
111 03-DEC-2012 25
111 04-DEC-2012 20
*** **** ****
111 31-DEC-2012 28

So i am expecting my query out put should be in column wise, that should make columns dynamically based on dates.

like example

__Partno     01-Dec-12     02-Dec-12     03-Dec-12 04-Dec-12     ----     ----     ---     31-Dec-12__
111     0 15 25 20 28     

Here columns should be created based purchase dates dynamically with respect to quantity. Query out put will be like matrix format. So i feel that PIVOT & SYS_CONNECT_BY_PATH will not serve my requirement.

My system comparability is

oracle 10g database.
oracle 6i forms.                         

View 4 Replies View Related

SQL & PL/SQL :: Generate Columns Dynamically By Depending On The Rows In A Table In 11G?

Nov 27, 2012

Is there any way to generate columns dynamically by depending on the rows in a table in 11G .

Ex: If the deptno in DEPT table is not constant,then how to generate the N numbers of columns based on the deptno. Below query is working when we hard coded the deptno (10,20,30,40).What else if we more number of departments and we don't know the departments also.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as dbo

SQL> SELECT *
FROM (SELECT deptno, job, sum(sal) sal
FROM SCOTT.emp GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(10,
20,
30,
40));

[code].....

View 4 Replies View Related

SQL & PL/SQL :: Construct Query To Fetch Different Rows From Same Table In Different Columns

May 25, 2013

Lets say I have a table in ORACLE database like:

ACC_ID | ACC_AMT
111 | 10000
111 | 12000
111 | 14000
222 | 25000
222 | 30000
333 | 18000
333 | 27000
333 | 13000
333 | 15000

I want to get the output as:

ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
111 | 10000 | 222 | 25000 | 333 | 18000
111 | 12000 | 222 | 30000 | 333 | 27000
111 | 14000 | null | null | 333 | 13000
null | null | null | null | 333 | 15000

I need each different ACC_ID with ACC_AMT in different columns. The table may have other different ACC_ID also, but I will fetch only what I need. What is the best way to do this?

So far I have tried this:

SELECT
(CASE WHEN ACC_ID=111 THEN ACC_ID END) AS ACC_ID_1,
(CASE WHEN ACC_ID=111 THEN ACC_AMT END) AS ACC_AMT_1,
(CASE WHEN ACC_ID=222 THEN ACC_ID END) AS ACC_ID_2,
(CASE WHEN ACC_ID=222 THEN ACC_AMT END) AS ACC_AMT_2,
(CASE WHEN ACC_ID=333 THEN ACC_ID END) AS ACC_ID_3,
(CASE WHEN ACC_ID=333 THEN ACC_AMT END) AS ACC_AMT_3
FROM <TABLE_NAME>

But I am not getting the desired result.

View 22 Replies View Related

SQL & PL/SQL :: Update Column In A Table Which Has 3 Columns Of 16 Million Rows?

Apr 25, 2012

trying to update a column in a table which has 3 columns of 16million rows from column in another table which has 1million rows, there is no relationship between the 2 tables.

Table A has 3 columns of 16million rows, the first two columns have 16million ID numbers, the 3rd colunm is currently NULL.

Table B has 1million Numbers, i need to somehow update column 3 in table A using the numbers in table B, it doesnt how many times each of the 1 million numbers are used but i dont want it to just update every row to the same value.

View 13 Replies View Related

SQL & PL/SQL :: Rows To Columns?

Apr 5, 2012

Sample data

col1 col2 col3
1 A someval1
2 A someval2
3 A someval3
2 B someval4
3 B someval5

In col1 there will be always 1 or 2 or 3 value not more than 3 I am using oracle 10g.Want the following output in a single query with using user defined function or stored proc

OUTPUT
newcol1 newcol2 newcol3
someval1 someval2 someval3
null someval4 someval5

View 3 Replies View Related

Converting Rows To Columns Using OWB

Aug 22, 2013

Getting error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR Source row:

NOTE_IDCONTRACT_GRANT_IDPROSPECT_IDPROGRAM_CODE
1 1 1 786
2 2 2 786

Program:

SELECT
CASE
WHEN "PIVOT_ROW_GENERATOR"."ID" = 0 THEN
"PIVOT_SOURCE"."ID_NUMBER"
WHEN "PIVOT_ROW_GENERATOR"."ID" = 1 THEN
"PIVOT_SOURCE"."ID_NUMBER"
[code].........

View 3 Replies View Related

SQL That Treats Rows As Columns

Oct 29, 2008

Note: This is not a homework assignment, but rather, a technical bottleneck at work.

My dilemma is such: let's say that I have 1 teacher in an entity, and this teacher has 3 students in an associative entity. So if you did a select T.teacher, S.student from TEACHER T, STUDENT S, where T.teacher_id = S.teacher_id, you would get 3 rows:

Teacher 1, Student 1
Teacher 1, Student 2
Teacher 1, Student 3

How I would like to display it, is in 1 row:

Teacher 1, student 1/student 2/student 3

Is there a way to write a SQL to achieve the above?

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

How To Convert Rows To Columns

Jun 18, 2011

how to convert rows to columns ?

View 1 Replies View Related

Convert Rows Into Columns

May 30, 2007

Below is the schema of a table:

TableName : PropertyValue

Columns: PropertyID Number
Value varchar
ValueID Number
Phone Number

Requirement: Create a view based on the table"PropertValue'. There could be 4 different PropertyIDs for each phone.

e.g.
PropertyID Value ValueID Phone
1 'xyz' null 1234
2 null 11 1234
3 null 12 1234
4 null 13 1234
1 'pqr' null 5678
2 null 14 5678
3 null 15 5678
4 null 16 5678

Required View:

Phone Attrib1 Attrib2 Attrib3 Attrib4
1234 'xyz' 11 12 13
5678 'pqr' 14 15 16

I tried creating the required view using 'CASE' statement and group by but its returning multiple rows.

select case when PropertyID=1 then VALUE end as Attrib1,
case when PropertyID=2 then ValueID end as Attrib2,
case when PropertyID=3 then ValueID end as Attrib3
case when PropertyID=4 then ValueID end as Attrib4
from (
select Phone, PropertyID, ValueID,Value
from PropertyValue
group by Phone, PropertyID, ValueID,Value
)

View 1 Replies View Related

SQL & PL/SQL :: Transform Rows Into Columns

Aug 15, 2011

Table T having columns Name, TCode, TCount...Sample Data:

Name, TCode, TCount
Joe,X,5
Bloggs,X,7
Joe,Y,9
Smith,Y,2
Bloggs,Z,3

This data to be represented as:
Name,X,Y,Z
Joe,5,9,0
Bloggs,7,0,3
Smith,0,2,0

View 4 Replies View Related

Convert Columns Into Rows?

Aug 3, 2009

i have a table with this data :

id name
1 a
2 b
3 c
4 d

and i want the o/p like this,

col1 col2 col3 col4 col5
id 1 2 3 4
name a b c d

means i want to convert my columns into rows.

View 1 Replies View Related

SQL & PL/SQL :: How To Parse Rows In Different Columns

Apr 29, 2011

I have a table that has rows like below in one column. I want to parse the rows in different columns.

Column12

abc|yed| qas ert | hub cap | jak
agh|
bef|rfd
som|gfr|lup

I need to parse the above in different columns by splitting at the pipe.

so it will show something like this

col1 col2 col3 col4 col5
abc yed qas ert hub cap jak
agh
bef rfd
som gfr lup

is it possible to do it in PL/sql.

View 3 Replies View Related

SQL & PL/SQL :: Result From Rows To Columns

Oct 29, 2013

How can i retrieve result of multiple rows into single row. Ex

select ename from emp;

Ename:
--------
ALLEN
WARD
JONES
MARTIN
BLAKE

[Code]...

Result should be like

ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

View 8 Replies View Related

SQL & PL/SQL :: Rows To Columns With Headings

Feb 11, 2011

I have a table with the following structure and data.

create table testing_1 (column_1 varchar2(10),
heading_1 number ,
value_1 number);
insert into testing_1 (COLUMN_1, HEADING_1, VALUE_1)
values ('First', 10, 99);
[code].......

COLUMN_1 HEADING_1 VALUE_1
First 10 99
Second 50 50
Third 10 80
First 50 77
Third 50 70
First 100 55
Third 100 60

And i need the output like below. (i.e. distinct heading_1 (10, 50, 100) will become our new column headings and corresponding values for these headings will be filled in all the rows)

10 50 100
First 99
Second 50
Third 80
First 77
Third 70
First 55
Third 60

View 6 Replies View Related

SQL & PL/SQL :: Convert Rows As Columns

Jun 7, 2010

Need to convert columns into rows like expected

service_key consumer_key the_key fin_score_type granu rno

20100201 1 p_1 100MONTH1
20100201 1 p_0 100MONTH1
20100201 1 d_6 100MONTH1
20100201 1 t_2 200MONTH1
20100201 1 d_5 100MONTH1

But it should display like below,All the the_key types as differnet colmns instead of rows,Since need to insert those values in different tables.

expected o/p consumer_key p_code p_val d_code d_val t_code t_values granula

service_key
20100201 1 p_0 100 d_6 100 t_2 200 MONTH1
20100201 1 p_1 d_5 100 MONTH1

View 5 Replies View Related

SQL & PL/SQL :: Query To Get Columns Into Rows?

Aug 10, 2010

I have one issue with the converting columns into rows.

Employee table has manager , additional manager and some other columns. If the employee has additional manager, then i need those employee record with 2 rows : 1st row - manager with other data , 2nd row - additonal manager with other data .

Orginal data is like this:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ADDTIONAL_MGR ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 7698

[code]....

I have tried this , using UNION ALL method, But it is taking more time.

SQL> ED
Wrote file afiedt.buf
1 SELECT DECODE (DUMMYTAB.NUM,'1',MGR, '2',addtional_mgr) MANAGER, empno, ename, job, hiredate, sal, comm, deptno
2 FROM emp E, (SELECT '1' NUM FROM DUAL UNION ALL SELECT '2' NUM FROM DUAL) DUMMYTAB
3* WHERE addtional_mgr IS NOT NULL
SQL> /

View 15 Replies View Related

SQL & PL/SQL :: Getting The Rows And Columns Values

Sep 3, 2010

I want to get the values and put them into html template since i want to configure mime settings. My table's name is rawticket_voip and it has 150.000rows and 20 columns so instead of COL1, COL2 what should i write there? How can i get the spesific row's and column's value and how can integrate loops in this html?

'<html>
<table border="1">
<tr bgcolor="Light Blue">
<td>COL1</td>
<td>COL2</td>

[Code]...

View 4 Replies View Related

SQL & PL/SQL :: Columns Into Rows Conversion

Mar 24, 2011

I have the following query

select a.EV_ACTOR_NAME, round((b.total_transfer/(24 * a.Max_Throughput)) * 100) AVG_ThroughPut,round(((b.total_transfer/a.Max_Throughput) * 100 )) MAX_Throughput from
(select distinct EV_ACTOR_NAME,((max(EV_TRANSFER_RATE) over ( partition by EV_ACTOR_NAME)) * 60 * 60) /1024 Max_Throughput from dprt_events where EV_ACTOR_NAME is not null ) a,
(select distinct EV_ACTOR_NAME, (sum(EV_TRANSFER_SIZE) over( partition by EV_ACTOR_NAME)/1024/1024/1024) total_transfer from dprt_events where EV_ED_ID in (40,105,112) and EV_END_TIME between (sysdate - 20) -12/24 and sysdate - 20) b
where a.EV_ACTOR_NAME = b.EV_ACTOR_NAME

This is the output i get :

EV_ACTOR_NAME AVG_THROUGHPUT MAX_THROUGHPUT
------------ ------------- --------------
actor2 1 33
actor3 0 0
actor1 2 53

How do I convert the above out put to following format.

EV_ACTOR_NAME value1 value2
-------------- -------------- --------------
actor2 AVG 1
actor2 MAX 33
actor3 AVG 0
actor3 MAX 0
actor1 AVG 2
actor1 MAX 55

I tried the following

WITH data AS
(select level a from dual connect by level <= 3)
select EE_ACTOR_NAME,decode(a, 1, AVG_ThroughPut, 2, MAX_Throughput) value from
(

[Code]...

but i always get the following error ORA-00904: "A": invalid identifier

View 5 Replies View Related

SQL & PL/SQL :: Convert Rows To Columns?

Aug 2, 2010

consider the following tables

WITH table1 AS -- this table contain a list of column names
(
SELECT 11 cid, 'TEK' group_nm, 'TYPE DESC' column_nm FROM dual
UNION ALL

[Code].....

i have 3 tables, one that contains name of columns(table1), another one contain number of rows (table2) and the last table contain

the values for columns in table1 for each row in table2.

what i want to do is join all 3 tables and display the output as follow

TYPE DESC NAME P DATE TYPE_PCT
====================================================
Good John 8/21/2010 0.009
BAD Ken 4/11/2010 10.009
Medium Rob 8/1/2010 0.001

as you can see the columns names comes from table1, and the values comes from table3. i want to join these tables so that it display the output above

View 2 Replies View Related







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