SQL & PL/SQL :: Converting Rows To Columns Dynamically?
Oct 20, 2011
I Have a table like this. 12 rows and 8 columns.And column 1 Dt is going to change everytime,as date and months proceeds.and corresponding columns values are going to change.
I want to convert rows to columns dynamically..
DT COL1COL2COL3COL4COL5COL6 COL7
01/01/20111234567
01/02/2011131415161718 20
01/03/2011252627282930 32
01/04/201137383940414201/05/2011495051525354 55
01/06/2011616263646566 67
01/07/2011737475767778 29
01/08/2011858687888990 92
01/09/2011979899100101102 04
01/10/2011109110111112113114 115
i want result like this.
01/01/201101/02/201101/03/201101/04/2011
1 9 11 12
2 11 23 12
3 11 11 11
4
5
6
7
8
View 2 Replies
ADVERTISEMENT
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
Sep 14, 2008
I have seen lots of examples of using PIVOT to return an sql query where the rows have been converted to columns. All the examples I can find require you to have two columns with multiple rows, and also require you to know the data which is in one of columns to make titles for the new columns.
I need something a little different...I have a query using UNION which goes something along the lines of...
SELECT [get data from one place]
UNION
SELECT [get data from another place]
UNION
SELECT [get data from another place]
this returns the following...
a_val
-----
100
200
300
query to return something like this...
a_val1 a_val2 a_val3
------ ------ ------
100 200 300
The names of the columns don't really matter. And I dont want to create a new table in the database just for the result.
View 1 Replies
View Related
Jul 5, 2012
Is it possible to communicate with a serial device (via COM1) with oracle forms / webutil? I am able to communicate in java using RXTXcomm, but I am having problems making a bean that uses it (getting security errors and yes, I have signed the jars).
Basically, I have a scale hooked up to my COM1 port and want to be able to send commands (zero and tare) to it and read the weight from it through a form or java bean.
View 6 Replies
View Related
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
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
Sep 6, 2012
I have a row which contains 6 columns where I want that data to be shown in the form of columns as shown here:
From:
select item1, item2, item3, amt1, amt2, amt3 from item_table where sno=1; <----- returns 1 row as below
ITEM1 ITEM2 ITEM3 AMT1 AMT2 AMT3
---------- ------------ ------------- ----------- ------------ ----------
AAA BBB CCC 10.00 20.00 15.00
Data explanation: item1's (AAA) price is amt1 (10.00), item2's (BBB) price is amt2 (20.00) and item3's (CCC) price is amt3 (15.00). OK.
Now I want that data to convert into columns as shown here:
To:
ITEMS AMT
--------- ---------
AAA 10.00
BBB 20.00
CCC 30.00
I want a SQL to display this data.
I found one query which converts a row into columns, but this does not serve my requirement: [for your reference only]
SQL> select substr( the_string
, decode( level, 1, 1, instr(the_string,',',1,level-1)+1)
, decode( instr(the_string,',',1,level), 0, length(the_string), instr(the_string,',',1,level) - decode( level, 1, 0, instr(the_string,',',1,level-1))-1)
) the_value
from ( select (select item1||','||item2||','|| item3 from item_table where sno=1) ITEMS
from DUAL)
connect by level <= length(the_string)-length(replace(the_string,','))+1
View 11 Replies
View Related
Aug 16, 2007
i 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.
View 2 Replies
View Related
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
Jul 14, 2012
I have two tables as follows:
TABLE_1
ID ENTRY_DATE VALUE
------- --------------- ----------
1 1-JUN-12 21
1 2-JUN-12 51
1 3-JUN-12 232
1 4-JUN-12 221
1 5-JUN-12 424
.
.
.
.
1 26-JUN-12 52
1 27-JUN-12 0
1 28-JUN-12 247
1 29-JUN-12 528
1 30-JUN-12 489
2 1-JUN-12
2 2-JUN-12
2 3-JUN-12
2 4-JUN-12
2 5-JUN-12
.
.
.
.
2 26-JUN-12
2 27-JUN-12
2 28-JUN-12
2 29-JUN-12
2 30-JUN-12
TABLE_2
ID DAY_1 DAY_2 DAY_3 DAY_4 DATE_5 .......... DAY_26 DAY_27 DAY_28 DAY_29 DAY_30
----- ---------- --------- --------- ---------- ---------- --------- --------- -------- -------- --------
1 21 51 232 221 424 52 0 247 528 489
2
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.
View 1 Replies
View Related
Jul 9, 2013
I have to audit some table to log all modifications (inserts, updates, deletes). I wrote the following trigger (works as expected) :
-- Trigger for DOCUMENT table audit
CREATE OR REPLACE TRIGGER TAUDIT_IUD_DOCUMENT
AFTER INSERT OR UPDATE OR DELETE ON DOCUMENT
FOR EACH ROW
DECLARE
l_user VARCHAR2(64);
[code].........
Is there any way I can improve this ?I mean getting columns name and value (:NEW and :OLD) dynamically, instead of specifying them one by one (the trigger will have to be updated in case a new column is added). Something like this :
FOR i in 1..DOCUMENT.COLUMN_COUNT LOOP
l_row := l_row || DOCUMENT.COLUMN_NAME(i) || :NEW.COLUMN_VALUE(i) || l_separator;
END LOOP;
View 6 Replies
View Related
Apr 26, 2010
I want to pass Number of columns dynamically to a query. I got success in SQL.
SQL> select &column_list from emp;
Enter value for column_list: empno,ename,sal
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
14 rows selected.
But the same i need to achieve in pl/sql. I try with the Ref cursor, but not succeeded.
View 15 Replies
View Related
Aug 5, 2013
I am trying a pl/sql block which which take a string and execute it dynamically. Suppose below is string
M_COL := Q'[(P_CODE=> ':DEPTNO',P_CODE_TYPE => 'STATE')]';
Now trying to execute it. using below
M_STR := 'SELECT CHK_DEPT' || M_COL || ' FROM EMP WHERE EMPNO=''7499''';
EXECUTE IMMEDIATE M_STR
INTO M_DATE;
Now what i want is M_STR vairable to executed as
SELECT CHK_DEPT(P_CODE=> DEPTNO,P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
instead of
SELECT CHK_DEPT(P_CODE=> 'DEPTNO',P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
other in other way in parameter P_CODE Column value of DEPTNO should be passed.Also note that DEPTNO column in string M_COL is Dynamic.i.e
M_COL := Q'[(P_CODE=> ':DEPTID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':EMP_ID',P_CODE_TYPE => 'STATE')]';
View 9 Replies
View Related
Jan 25, 2013
im trying to select columns from different tables dynamically in a function . The parameter for the function will be table name and column id's, In this number of columns may vary . Is it possible to have dynamic %rowtype to store the cursor value in it.
View 2 Replies
View Related
Jul 31, 2013
how to assign values from a "rule table" to a rowtype-variable. The ruletable contains values for different columns in different tables.Now i need to assign those given values for given columns out of that rule table to the equivalent column in a rowtype-variable.
CREATE TABLE TBRULES
(
TABLE VARCHAR2(50 BYTE)
, COLUMN VARCHAR2(50 BYTE)
, VALUE VARCHAR2(200 BYTE)
);
[code].....
View 15 Replies
View Related
Jun 13, 2010
I want to make a trigger , to store values that are affected during any DML(insert,update,delete).
CREATE OR REPLACE TRIGGER generate_log
BEFORE INSERT OR UPDATE
ON EMPLOYEE
FOR EACH ROW
DECLARE
[code]....
View 8 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
Feb 17, 2011
My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
View 10 Replies
View Related
Mar 24, 2011
where I have to display the value of current cell in next following cells.The table structure is as follows :
ttdate - date
individualplanid - varchar(10); - train number
sch_deptime - number(8); - scheduled departure time in milli seconds
sch_arrtime - number(8); - scheduled arrival time in milli seconds
stn - varchar(10); - station short name
dep_delay - number(8); - dep delay in milli seconds
arr_delay - number(8); - arr delay in milli seconds
The delay is filled continuously by application software continuously.I want to make a query where I want a calculated field which does the prediction of train arrival on coming stations. This shall be done as the delay + sch_deptime for all the next stations. Following is a dataset :
individualplanid sch_deptime sch_arrtime stn arr_delay dep_delay
BO646NULL57900000BVINULL80000
BO646NULL58140000KILENULL40000
BO646NULL58320000MDDNULL20000
BO646NULL58530000GMNNULLNULL
BO646NULL59160000ADHNULLNULL
BO646NULL59550000STCNULLNULL
BO646NULL59940000BANULLNULL
BO646NULL60540000DDRNULLNULL
BO646NULL61200000BCLNULLNULL
BO64661800000NULLCCG12000NULL
[code]....
The last column (EAT) is calculated one. It shall be like as the dep_delay is updated the EAT for the following records shall be sch_deptime + dep_ delay. I did was something like this. The calculated field is cum_depdelay which is only the delay ( not sch_deptime + dep_delay ).
select ttdate,individualplanid td,station,sch_deptime,sch_arrtime,act_depdelay,
sum(act_depdelay) over ( partition by individualplanid order by sch_deptime rows between current row and unbounded following ) c_depdelay
from logtime where ttdate='14-Mar-2011' and individualplanid='BO646' order by sch_deptime,sch_arrtime;
[code]...
how shall i proceed ?
View 2 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
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
