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

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

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 :: Generate The Number Of Rows Based On Table Field?

Mar 29, 2013

generate the number of rows based on table field.

Sample code is given below.

CREATE TABLE T
(
docno VARCHAR2(10),
CODE VARCHAR2(8),
QTY NUMBER(3)
)
LOGGING

View 4 Replies View Related

SQL & PL/SQL :: SELECT With Count Of Rows Depending On Sum Of A Field?

Aug 10, 2011

I want to create a SELECT, that shall give back only a special amount of rows, depending on the sum of one of the selected fields.

At first a code sample of the complete selection:

SELECT DISTINCT mnr, ktxt,
(SELECT Sum(meng_4)FROM reldb d1 WHERE d1.mnr=d.mnr)qty
FROM reldb d
WHERE mnr IN (SELECT mnr FROM relac WHERE Lower(rlnr) NOT LIKE 'platte geprägt%')
AND saext='M'
ORDER BY qty DESC,ktxt;

This selection produces some lines of output (in my case i.e. like 300). What I want to see is only that much lines that the condition 'sum of all items listed below meng_4<=sum of all items meng_4 of the whole selection * 0.9' is fulfilled.

So, if the whole selection produces a total of 10000 as sum for all items meng_4, I want to see only that amount of rows that sums a total of at least 9000 for all items meng_4.
I hope, this specification is exactly enough to understand my intent.

1. Can I do this in a query?

2. If yes, what would this query look like?

View 19 Replies View Related

SQL & PL/SQL :: Generate Range Based On Start And End Number Dynamically?

Jul 14, 2011

I have following requirement. Let say i have to generate a range based on "start number" and "end number" dynamically.Some kind of hash buckets.

e.g Start Number : 1 and End Number : 1001.

Also i want to divide that range based on some dynamic value like for above example 10 ranges of 100 each. and both 1 and 1001 should be included only once and the next row start number cannot be same as previous row end number.

Means

Range 1 1 -101
Range 2 102-200
Range 3 201-300
...
Range 10 901-1001

Is there any way of doing it automatically. I tried with Model clause. it works fine for even cases but for odd i have issues also when i take small start and end number i get an error.

SELECT case when ranges=1 then ranges else ranges+1 end Start_Id, ranges+round((1001-1)/10) End_Id
from
(

[Code].....

I want this to be generic for any values here 1 is Start Number 1001 is end number and 10 is the bucket. I need these parameters dynamic and want's the same kind of results for any values.

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

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

PL/SQL :: Get Columns Name And Value Dynamically From Trigger

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

SQL & PL/SQL :: How To Pass Different Number Of Columns Dynamically To A Query

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

Forms :: Dynamically Passing The Columns Values?

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

SQL & PL/SQL :: Select Columns From Different Tables Dynamically In A Function

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

PL/SQL :: Dynamically Assign Values To Different ROWTYPE-Columns By Ruletable

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

SQL & PL/SQL :: Audit Trail Trigger To Dynamically Pick Columns During DML?

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

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

SQL & PL/SQL :: Dynamically Concatenating Column Values Based Upon Number Of Grouped Columns

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

Reports & Discoverer :: Dynamically Displaying Current Value In Rows

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

SQL & PL/SQL :: Multiple Rows On A Table To Multiple Columns On One Row

Nov 26, 2010

I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below

TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan

My desired result is to be able to show the output as follows

Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan

View 6 Replies View Related

Reports & Discoverer :: Generate Excel Report Output With Maximum No Of Rows?

Nov 24, 2011

I created an xml report of output type 'xsl'. When the report output is generated for more than 65,536 rows then the report out put is not opening. generate excel report output with maximum no of rows.

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