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
ADVERTISEMENT
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
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
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
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
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
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
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
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)
pivot
(sum(wages)
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
Aug 16, 2010
I am facing with one problem while creation of function.
CREATE OR REPLACE FUNCTION fun1(
v_finyear VARCHAR2,
v_yrno NUMBER,
[Code].....
The function created successfully...
But it return the wrong values, like
SQL> SELECT fun1('2004-05', 8, 'FEB')
2 FROM DUAL
3 ;
TB('2004-05',8,'FEB')
--------------------------------------------------------------------------------
FEB
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
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:
1 DECLARE
2 v_sql VARCHAR2(32767);
3 v_refcur SYS_REFCURSOR;
4 BEGIN
5 v_sql := 'SELECT deptno ';
[code]....
View 11 Replies
View Related
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
loop
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
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
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:
SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS
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....
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
FIRST_NAME varchar2(200);
LAST_NAME varchar2(200);
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS';
[code]....
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.
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_tab;
l_dummy NUMBER;
l_value NUMBER;
[code]....
View 7 Replies
View Related
May 18, 2010
converting Rows to Column for a specific group.
Following is the query
SELECT Lpad(papf.employee_number, 6, '0') "File#",
papf.full_name,
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),
0),
[Code]...
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
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
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
a
b
c
d
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
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
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
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
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
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
Jun 18, 2011
how to convert rows to columns ?
View 1 Replies
View Related
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
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
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
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
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
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
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
