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

Jul 15, 2013

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

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


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)

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


View 11 Replies View Related

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

SEPTEMBER 200917790017790
OCTOBER 2009183831278818347
NOVEMBER 2009177901460517762

and I need to display this as

RMS 17790 18383 17790
RMS_OCC 0 12788 14605
TTL_RMS 17790 18347 17762

View 3 Replies View Related

SQL & PL/SQL :: Rows To Columns With Dynamic Allocation Column Names

Nov 16, 2012

I want the output from two tables with rows to columns and generate dynamic basing on the columns.

For example: Table A (a1) , Table B(a1,b1)

Data: A B
------ ----
a1 a1 b1
--- ---- ----
1 1 x
2 1 y
3 2 a
2 b
2 c

o/p: Columnname col_1 col_2 col_3
a1 b1_1 b1_2 b1_3
----- ---------------------
1 x y
2 a b c

Columns should be generated based on the second table second column.

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

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

Jul 26, 2013

I am using Oracle Database 11g Enterprise Edition Release - 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 -

- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        5000       2000      12600      20000      45600      35000      45000      56000       65000


View 7 Replies View Related

SQL & PL/SQL :: Script To Generate Dynamic Columns

Mar 6, 2011

Is there any sql script to generate dynamic columns? pivot still needs the programmer to know the data to generate columns just like this.

select * from
(select current_position, fk_department, wages
from employee)
for fk_department in ('INT', 'WEL', 'CEN', 'POL'));

how about if i don't know the data in fk_department. is this possible in sql?

View 39 Replies View Related

SQL & PL/SQL :: How To Pass Dynamic Columns In Functions

Aug 16, 2010

I am facing with one problem while creation of function.

v_finyear VARCHAR2,
v_yrno NUMBER,


The function created successfully...

But it return the wrong values, like

SQL> SELECT fun1('2004-05', 8, 'FEB')
3 ;


SQL> spool off;

2004-05 is the value of TABLE1
08 is also value of TABLE1
FEB is the Column_name of TABLE1

Actually, i have a value in FEB month, but it return FEB. FEB is the column_name of 'TABLE1'

View 1 Replies View Related

SQL & PL/SQL :: How To Get Dynamic Columns Using Default EMP Table

Aug 18, 2011

i am practicing to get Dynamic columns as i have learnt from Orafaq SQL/PLSQL forum .i am using Default EMP table...first one is running smoothly as below:

2 v_sql VARCHAR2(32767);
3 v_refcur SYS_REFCURSOR;
5 v_sql := 'SELECT deptno ';

View 11 Replies View Related

Storing The Values Of Dynamic Columns In Oracle

Nov 18, 2012

I have a requirement to execute the query only once for all the columns mapped to a particular table. Lets take an example..

table t1 has the following columns mapping.

col1 col2 col3 col4
variable 1 table abc col1_abc ----
variable 2 table abc col2_abc ----
variable 3 table xyz col1_xyz ----
variable 4 table xyz col2_xyz ----
variable 5 table xyz col3_xyz ----
variable 6 table xyz col4_xyz ----

In col4 , we have to update the values of col3 (column) ... it is very easy to update the values by executing the query for each column but my requirement is ... I want to execute the query once for a table ( example select col1_abc,col2_abc from table_abc ... this will return always a single value after adding other conditions in where clause) and why I want to do this? .. because there can be multiple (might be 30 variables values)
from a single table.

I have converted the columns into rows by using listagg function now I have lets a dynamic query ( for the above example).

for i in 1.2

l_query := ' select col1_abc, col2_abc from table_abc';
open c1 for l_query;

end loop;

my problem is : how to hold these values and update the above table?

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Resultset For Linear Growing Columns In A Table?

May 7, 2013

Provided a sample table to determine the sales agents performance below.

Id AgenName Zone1 Zone2 Zone3 Zone4
1 ABC 90 85 65 55
2 SDD 45 67 78 90
3 ERF 85 30 52 45

If in any zone, the sales is less than 40, it should be failed.This can be determined using the below query

select Id, AgenName, Case when Zone1 < 40 or Zone2 < 40 or Zone3 < 40 or Zone4 < 40 Then 'Failed'
Else 'Eligible'
End as Flg_Comm
from sales_table;

But the problem is when the zone grows (zone5, zone6) every time the query requires to be added with or condition.

Is there a way to dynamically determine the result even when no. of columns grow (or added)?

View 1 Replies View Related

PL/SQL :: Write Dynamic SQL To Query Unknown Number Of Columns

Jun 6, 2012

I need to write a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a simple example query here:


If I have known the number of columns, e.g. querying two columns: "FIRST_NAME" and "LAST_NAME", I can write a DYNAMIC SQL based on the template in table 8-2 of URL....

stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
FIRST_NAME varchar2(200);
LAST_NAME varchar2(200);
cur_hdl := dbms_sql.open_cursor; -- open cursor

However, if I wish to write a dynamical sql to query these two columns for a more general purpose (which should meet the requirement to query different number of columns, e.g. three columns, FIRST_NAME, LAST_NAME, BIRTHDAY instead of two columns FIRST_NAME and LAST_NAME). To do this I first try to query the same two columns but using a different method, following URL.....My code for the same query has error, and I cannot solve it.

stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_tab;
l_dummy NUMBER;
l_value NUMBER;

View 7 Replies View Related

SQL & PL/SQL :: Dynamic Rows To Column

May 18, 2010

converting Rows to Column for a specific group.

Following is the query

SELECT Lpad(papf.employee_number, 6, '0') "File#",
Decode(ppee.processing_type, 'R', Decode(ppee.element_name,
'Regular Wages USD', peev.screen_entry_value,

'Regular Salary USD', Round(peev.screen_entry_value / 26, 2),


Which gives me out put as

Example 1

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

View 7 Replies View Related

Forms :: Dynamic Columns - Dropdown Menu In Separate Block

Aug 27, 2013

I have 2 tables T1, T2

T1 is having 2 columns t1_c1, t1_c2
T2 is having 3 columns t2_c1,t2_c2,t2_c3

I need to design a form which should be like

there must be a pop item (Dropdown menu) in a separate block.

If I select 1 from the pop item,
T1 with 2 columns should display in a tabular form.
If I select 2 from the pop item,
T2 with 3 columns should display and T1 with 2 columns should hide.

So the form should be dynamic based on the selected pop item. Is there any chance to do this?

View 3 Replies View Related

Forms :: Dynamic Form According To Rows In Another Table?

Oct 28, 2010

i want to create a form & show some columns accordingly rows entered in another table

for eg.

a table name temp have three columns (no,name,date) in name it shows some rows like

i want to create a new dummy form which shows
a b c d

if i remove b from temp table then new form shows only
a c d

is it possible

View 2 Replies View Related

PL/SQL :: Populate Target Table - Dynamic Rows

Jun 21, 2013

create table src(id number,val number,data varchar2(100)) insert into src values (1,1,'SUN');
insert into src values (2,2,'WED');
insert into src values (3,3,'MON');     
create table trg(id number,val number,data varchar2(100)) required rows to be inserted in the target table. 

  insert into trg values (1,1,'SUNDAY');
insert into trg values (2,0,NULL);
insert into trg values (2,0,NULL);
insert into trg values (3,0,NULL);
insert into trg values (3,0,NULL);insert into trg values (3,0,NULL); 

 {code} based on the column value  of the source table src's column val , i need to populate my target table trg . If the value of val is 1 then  only one target row is created in the target .If the value of val in the source table src is 2 then the target is populated with 2 rows .The values of the target columns are mapped as follow:

1)id -as it is

2)val - if the val of src is 1 then map the val as it is .If the value of val is more than one then create as many rows as the value of val ,id will be as it is and the  value of val and data will be null

3)data - if the val of src is 1 then expand the abbreviation else null . 

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

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:

1 1 1 786
2 2 2 786



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


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.

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

This data to be represented as:

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.


abc|yed| qas ert | hub cap | jak

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



Result should be like


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

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

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

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