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


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

Application Express :: Color HTML Region Header Depending On SQL Query Result

Jul 24, 2013

How to color HTML region header depending on SQL query result ?

View 9 Replies View Related

SQL & PL/SQL :: Query To Transpose Column Names To Row

Mar 9, 2011

Query : select * from scott.dept where deptno=10;


But i want the above resultset as below,


View 4 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 :: Selecting Value Of Column Into Different Variables Depending On Condition?

Aug 10, 2011

I need to select the value of a column into different variables depending on the condition.

As in, I have


I need to get the Rention days into the 4 variables v_color_file_type_code, v_size_file_type_code, v_buyer_file_type_code AND v_uom_file_type_code Depending whether the FILE_TYPE_CODE is 'CLR' or 'SIZ' or 'ADB' or 'UOM'

View 3 Replies View Related

SQL & PL/SQL :: Setting Column Value Depending On Number Of Month

Aug 1, 2011

My need is to calculate rows and write result into column:

c1 if current month is 1st in quarter,
c2 if current month is 2nd in quarter,
c3 if current month is 3rd in quarter.

How do I perform it?

View 22 Replies View Related

Export/Import/SQL Loader :: How To Upload Linux File To DB Depending On Column Name

Apr 15, 2013

I'm using Oracle Database 11g R2 need to upload Telecom CDRs to the database on daily basis , it's huge data and changeable , an example of my file in linux Redhat 5 server as below ,

INDRtotalduration = 00:00:00
origin_matrix = 4186603ec003ef01
triggering_key = 665000207
Start_Date_And_Time = 03/04/2013 09:24:10
IMSI = 418666651000207

there is no problem with this i think i can use SQLLDR to upload this file , but the problem here the positions of the columns in the file could change depending on user behavior it could be the first row comes in the third row or any row and maybe more rows appears ,

locind = 0
origin_matrix = 4186603ec003ef01
Start_Date_And_Time = 03/04/2013 09:24:10
INDRtotalduration = 00:00:00
IMSI = 418666651000207
triggering_key = 665000207

this is sample of the file i could be more than 100 rows , and the position of the field and field names could be change every time depending on the Subscriber usage , is there any way to upload the file but after checking the field name in the file and matching to corresponding column name in the table .

View 5 Replies View Related

Application Express :: Showing Particular Row In A Color Depending On A Column In Classical Report

May 31, 2013

I have a classical report with a column as "Affirmation Status". This column can take three value as "Affirmed, Rejected or NA". My requirement is I have to show all row in one color like red wherever it has value affirmed, and all row in color like green wherever it is rejected.

View 4 Replies View Related

Application Express :: Setting Value Of Column Depending On LOV Selection Tabular Form

Mar 7, 2013

We have a master detail form with report that inserts the details in both region/ sub region.

However our requirement is that in sub region we have a LOV to chose the service, and each service has a price. How can we populate the Price column based on the selection of the Service? There are some similar posts but we cannot apply to our form.

Application Express
Oracle Database 11g Express Edition Release

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically


500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494

View 1 Replies View Related

SQL & PL/SQL :: Code For RESULT Column?

Aug 4, 2011

I have the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:

create table zTEST
( IDx number,
STARTx number,
DURATION number,
RESULT number );
insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );


The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.

What is the sql code for the RESULT-column?

View 3 Replies View Related

SQL & PL/SQL :: Split Result Set Into Column

Aug 17, 2012

i have a table emp with three column

columns are (empid varchar,empnomini varchar,nominitype varchar), data in table like

empid empnomini nominitype
1 x B
1 y c
2 xx B
2 yyyy c

and i want data comes like

empid nominitype b nominitype c
1 x y
2 xx yyyy.

View 6 Replies View Related

Display Result Column 10610 In Second Row?

Mar 28, 2012

QUOTE (thiyagusham @ Mar 27 2012, 11:40 PM) SQL> select * from samp;

---------- --------------- ---------- -----
sam 21-mar-2012 mca
sony 03-jan-2000 mba 10610
maya 21-mar-2012 m.arch
sonna 18-mar-2012 mis


i want to display in result column 10610 in second row , because emp sony having id "10610 "should i use sub-query ? without sub-query how can i display ?

View 4 Replies View Related

SQL & PL/SQL :: Query Result Into XLS File With Column Names?

Oct 6, 2012

I want to load query result into .xls file with column names by using plsql.

Like this i need to generate 4 files and zip it then send mail with attachement of that zip file.

Can we automate in PLSQL? If it is possible pls share the script.

Ex: select ename,eno,dept,deptno,sal,hiredate from employee;

ENAME ENO DEPT DEPTNO SAL HIREDATE like this we need to store the query result into .xls file.

View 1 Replies View Related

PL/SQL :: Unexpected Result With Select Max (column) In Anonymous Block

Nov 27, 2012

The following query gives me 28,800 as sum(sal)

SELECT SUM(salary)
FROM employees
WHERE department_id =60
O/P is :  28800But when i use the above query in anonymous block it gives me 684400
v_sum_sal NUMBER;


The above output statements gives me 684400 as output.. But the expected is 28800

View 6 Replies View Related

PL/SQL :: Split Result Of Table Data Comparison To Key(s) / Column / Old - New Each Row

Nov 8, 2012

I have two tables T1 and T2. T1 is the original backup snapshot for changed records from overnight batch in a big table and T2 is the overnight batch changed records. Both tables have similar number of rows (T2 might have more for newly inserted rows) and you can find out the differences by comparing these two according to action column in T2 (C - Update, A - Insert and D - Delete)

how to compare these two tables to generate something like the following. I can join these two tables to generate the diff but it is one row per account.

client_nbr branch_cd, account_cd, action column, old_value, new_value
8888 123 45678 C account_clsfn_cd 004 005
8888 123 45678 C buy_cd 98 99
8888 012 34546 A sell_cd 12
8888 321 98765 D dividend_cd 1

I am using Oracle 10g so Unpivot cannot be used.



View 4 Replies View Related

PL/SQL :: How To Create Special Column Which Represents Result Of A Query

Feb 26, 2013

I have a table MESSAGE which has some billion entries. The columns are msg_id, vehicle_id, timestamp, data, etc.I have another table VEHICLE which holds static vehicle data (about 20k rows) such as vehicle_id, licenceplate, etc.

My first target was to partition the table via timestamp (by range) and subpartition by vehicle_id (by hash).So I could easily drop old data by dropping old partitions and tablespaces.

Now comes the new difficult 2nd target: the messages of some vehicles must be kept forever.My idea is to add a column KEEP_DATA to the table MESSAGE. I could try to partition by timestamp AND KEEP_DATA, subpartion by vehicle_id.The problem of this idea is that i have to update billions of rows.

It would be perfect if there is a possibility to add this KEEP_DATA-flag to the table vehicle.Is there any way to "link" this information to a column in MESSAGE table?

I mean something like this:

alter table MESSAGE
add column (select keep_data from vehicle where VEHICLE.vehicle_id = MESSAGE.vehicle_id as keep_message) ;

Is there some possibility like that? Would the partitioning on this column / statement work?Would the value of the keep_message be calculated on runtime?

If so will the performance influence be noticeable?If so will the performance also sink if the application is querying all rows except the keep_message?

View 4 Replies View Related

SQL & PL/SQL :: Get Dynamic Column Name And Corresponding Value

Jan 11, 2011

I want get dynamic column names and its corresponding values...Consider the below sample code

( ID NUMBER(5,0),

get the dynamic column values.

View 7 Replies View Related

SQL & PL/SQL :: Dynamic Column Name

Nov 17, 2011

How do I add a column (Status) to a select query based on the data returned.

Select Col1, Col2
Table 1, Table 2, Table 3
Table 1.Key1 = Table 1.Key1 and
Table 2.Key1 = Table 3.Key1 and
Table 1.Col1 in ('xyz','yty','ttr')

Output Expected:
Col1 Col2 Col3(Status)
xyx 1mst Sucess
yty Null No Data Found
ttr Null No Data Found

The Col1 value yty and ttr does not exist in the database

View 2 Replies View Related

Dynamic Column Resizing

Apr 26, 2010

I have a stored procedure that updates a series of columns (set at varchar(500) just to ensure there is ample space for whatever data is inserted into it) .Once it has updated though I've wanted to trim the column width down to the maximum size of the column value. I determine the max size of the column length and use that in a pl/sql command to ensure the width isn't smaller than the largest data entry.

When I try to do this I get the following message:

ORA-01441: cannot decrease column length because some value is too big

Now after hunting this down it seems as though you cannot decrease the size of a column once data has been inserted into it.

Any success in reducing the column width of a table that has data in it?

Why do I need to do this? I am exporting this table from Oracle into a propriety application and I am trying to reduce teh size of the export table by removing any unecessary space the unused spaces in the column. This will benefit the local app in addition to reducing the impact on the filesystem storage.

View 7 Replies View Related

Dynamic Old Column Name In Triggers?

Jul 26, 2004

I have column names stored in a table(say t1).

Inside a trigger I have to dynamically get the : old. values for only these columns.

This I need because during update trigger on a table (say t2) I am supposed pickup the coumn names stored in t1 and get the : old values of only these columns to insert into a new table .

View 5 Replies View Related

SQL & PL/SQL :: Dynamic Update Of Column Name?

Jan 23, 2012

i have creatd view,in which i need to show the entry of year in column wise,though the data is in row /record wise.By iteration i can manipulate the year in decode function like " (DECODE(a.Year,(select distinct max(year)-1 from BI_BALANCE_SHEET), a.Balance))",but is there any way to chachge the alias name /column name,here is the query

SUM(DECODE(a.Year,(select distinct max(year)-1 from BI_BALANCE_SHEET), a.Balance)) as year2010,
SUM(DECODE(a.Year,(select distinct max(year)from BI_BALANCE_SHEET) , a.Balance))as Year2011
from DEMO a

View 4 Replies View Related

SQL & PL/SQL :: Dynamic Column In Output?

Oct 15, 2013

I need to have columns dynamically defined in my SQL based on the column values in the table.

Selct emp_no,dept_no,grade from emp;
emp_no dept_no grade
1 10 A
2 20 B
3 20 A
4 10 C
5 10 A
6 20 C
7 10 B
8 20 C

I need the output as
dept_no Count_A Count_B Count_C
10 2 1 1
20 1 1 2

I can write hard coded sql to get this output but I need the count columns to be generated dynamically as there could be new entries in the grade column later. For example if there is a entry as D for the grade column my SQL should have a column count_D.

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Column And Values?

May 30, 2012

I have two string one is comma separated values as a column name and another string which is having the comma separated value corresponding to the column name.


now i have to create and run a query which will be like that

select * from emp where col1=1 and col2='apple' and col3='2-jul-75'

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

SQL & PL/SQL :: Dynamic Column Value Query?

Nov 3, 2010

I have a Strange requirement from client data is loaded from excel to Oracle Table- TST_TBL (with Header in Excel)



After the Data is loaded, we see data look like the above.

(1) Always COL3 column name have data value as 'Gen1' which is the indication for us from where data starts. But Gen1, Gen2, Gen3 etc... is dynamic. ie. This month we get gen1 and gen2 columns followed by null value in a column. Next month we get gen1,2,3,4 followed by null column.
(2) Null Column indicate us that there is a break in the column.
(3) Then next we need to look for next group of data (Monthly) and then insert into the same table again with different sheet_name column.
(4) Next for Quater and then YTD. None of the column Values are fixed and its all dynamic.

If you load the below data, you will come to know what i am looking for. I tried using UNPIVOT. But couldnt able to achieve it. Is there an option to do it in sigle query? or Do I need to go for Stored Procedure?

Insert into TST_TBL
(100, 'Wire_1', 'Gen1',
'Gen2', 'Gen3', 'Gen4');
Insert into TST_TBL


View 1 Replies View Related

PL/SQL :: Dynamic Column In A Cursor

Sep 1, 2012

So, for lack of a better way to explain it, I need to do the following.

I need a EXECUTE IMMEDIATE to be able to use a CURSOR but its saying its not declared. Is their a way to pass the cursor so a Execute Immediate can use it...


I know thats just a small part... but it shows the end goal. I need to append a VARCHAR that has the COLUMN NAME I need from the CURSOR. CUR_DATA is the open cursor. V_DATA is just a VARCHAR with text in it.

View 6 Replies View Related

SQL & PL/SQL :: Using Varchar Parameter In Queries With Number Column Can Result In Performance Degrade

Jul 4, 2013

I would like to know if using varchar parameter in sql queries with number column can result in performance degrade.

Ex: Procedure testa ( myparam varchar) is
select col1 into var1 from table where colno = myparam;

Here col no is a number column and myparam is varchar. I feel its better to change the parameter to number.

View 7 Replies View Related

PL/SQL :: How To Use Column Names Generated From Dynamic SQL

Feb 8, 2013

I have a problem with Dynamic SQL.

I have written an SQL which will dynamically generate the Select statement with from and where clause in it.

But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.

For that i have used a ref cursor to open and insert the table.

In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn

find below the sample code:


insert_csr ref_csr;

v_select VARCHAR2 (4000) := NULL;
v_table VARCHAR2 (4000) := NULL;
v_where VARCHAR2 (4000) := NULL;
v_ins_tab VARCHAR2 (4000) := NULL;
v_insert VARCHAR2 (4000) := NULL;
v_ins_query VARCHAR2 (4000) := NULL;


How to fetch the column names here
|| ');';


View 8 Replies View Related

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