SQL & PL/SQL :: Invert Column Data Into Columns
Apr 30, 2010
writing SQL, where I need to invert the column data into columns. My data in DB is as below.
MONTHNAMEVALUE
Jan-10M1 5
Jan-10M2 8
Jan-10M3 9
Feb-10M1 4
Feb-10M2 6
Feb-10M3 2
Feb-10M4 10
Mar-10M1 21
Mar-10M2 6
Required output for reporting will be like:
MONTHM1M2M3M4
Jan-10589
Feb-1046210
Mar-10216
How can I achieve this using SQL.[ORACLE 10g] ?
I can make use of Oracle BI server also.
View 2 Replies
ADVERTISEMENT
Mar 15, 2013
I have table t1 and (a1,a2,a3) are columns
I have this query
select a1 from t1
union
select a2 from t1
union
select a2 from t1
It's alternative is unpivot, but internally it calls table multiple times. I want a way, where I want the same output but calling the table only once.
View 4 Replies
View Related
Mar 26, 2012
I want to load single column data into multiple columns.
View 3 Replies
View Related
Aug 10, 2010
I just ponder how to write SQL or PLSQ if I'd like to get more columns into the one column.
For example:
I have table T1 where are 10 records. One columns TEXT is VARCHAR2. And I need to get by SQL/PLSQL one record with the new column VARCHAR2 where will be written 3 records as I wrote.
select TEXT from T1:
result: aa
bb
cc
And the desire result should lool like:
aa bb cc
how to quickly do it?
View 2 Replies
View Related
Nov 22, 2010
I am able to get a list of all the duplicate location_code values.
select count(*), location_code
from ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1
But how can I select all the other columns from ZZPN_PARKING_PORTFOLIO_801_V where the location_codes are duplicated.
select * from apps.ZZPN_PARKING_PORTFOLIO_801_V
where location_code in
(
select count(*), location_code
from apps.ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1
)
order by location_code
View 4 Replies
View Related
Mar 12, 2013
my need is to perform merge - update when id column is matched, but one of others columns not.When id column is not matched then I perform insert.
It works fine for matched or not matched id column.
Commented code is my try to perform check for others columns, The code should not update when all columns match. It should update only when on of columns doesn't match (except id column of course, because it's key column).
begin
merge into copy.table1 rr
using
(
select
ID ,
DEALID ,
ESTIMATIONDATE ,
BOUNDOVERESTIMATDATE ,
ESTIMATIONTYPEID ,
MARKETAMOUNT ,
LIQUIDATINGAMOUNT ,
[code]....
View 2 Replies
View Related
Jul 8, 2010
I want to add some new columns in an existing table that has over 10 millions rows, but not at the end of existing columns, my requirement is such i want new columns between existing existing columns. Is it possible.....
View 16 Replies
View Related
Jun 5, 2013
I have limited permissions and am unable to create temp tables.So I would like to use a cursor to "create" a table of sorts then access/query it. But this "table"/cursor would have no column names so how do I refer to the columns? Is there a way to refer to a column by column number rather than column name in a query:
select column1 from tablename where column2 = 'abc'?
Is there a way in a query/update/insert to refer to a column by column number rather than column name?
declare
cursor c1 is
select 'abc', '8-Apr-2013', pk_id from EMPLOYEE where pk_id = '153'
UNION
select '1xyz', '4-10-2013', pk_id from EMPLOYEE where pk_id = '154'
c1_val number;
[code]....
View 4 Replies
View Related
Sep 25, 2013
DB Used : Oracle 10g.
A table X : NUM, INST are column names
NUM ----- INST
1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1
-- Based upon requirement :
1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:
For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298
3) Save these values into
A table Y : INST, NUM are column names.
INST NUM
23 1234,1298
1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.
2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..
View 4 Replies
View Related
Dec 22, 2011
I need to update the current column with sum of the previous column values. Following are the creation scripts
DROP TABLE TEST_LOG;
CREATE TABLE TEST_LOG
(
[Code]....
Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.
I tried by using the following query
UPDATE TEST_LOG T SET SUM_PRE_COLS =
( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0)
FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);
View 5 Replies
View Related
Sep 23, 2013
Given a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.
View 10 Replies
View Related
Jul 31, 2012
I need to split a column into multiple columns. The data in my column is separated by a Comma (,). But the data is dynamic and I could have any number of data separated by (,).
Quote:FOR Ex:
If COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION, ITEM_DATA_TYPE
RESULT: should be 4 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION and ITEM_DATA_TYPE
Is COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION
RESULT:
should be 3 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION
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 28, 2012
column1 column2 column3 column4
12 Mar-21-2005 BDW blah blah blah
11 Feb-07-2001 ZV ha ha ha
12 Jan-02-2002 YM zuck zuck zuckI want a view that has that data like this:
column1 column2
12 Mar-21-2005 - BDW - blah blah blah; Jan-02-2002 - YM - zuck zuck zuck
11 Feb-07-2001 ZV ha ha haCan you help with SQL ?
I tried to use this Oracle LISTAGG function in the SQL, but got a "string concatenation limit exceeded"
View 3 Replies
View Related
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
Mar 30, 2012
I've a query that sum up data from one table. But I've to convert them into rows.
Summary query result.
COL1 COL2 COL3 COL4
1000 2000 3000 4000
Test Table creation script.
CREATE TABLE TEST_TBL
(
COL1 NUMBER(14,2),
COL2 NUMBER(14,2),
COL3 NUMBER(14,2),
COL4 NUMBER(14,2)
) ;
INSERT INTO TEST_TBL VALUES( 1000, 2000, 3000, 4000 ) ;
Expected result
A1 1000
A2 2000
A3 3000
A4 4000
A1, A2, A3, A4 are hard coded fixed values.
I could have done this but not a good idea in case table TEST_TBL is not a single row table but an inline query on 1,00,00,000 records with summary functions. In my table I've a summary query instead of single row table.
SELECT 'A1', COL1 FROM TEST_TBL
UNION ALL
SELECT 'A2', COL2 FROM TEST_TBL
UNION ALL
SELECT 'A3', COL3 FROM TEST_TBL
UNION ALL
SELECT 'A4', COL4 FROM TEST_TBL
View 12 Replies
View Related
Aug 16, 2011
i want to select columns of 3 tables in such a way that period column should be in the group by function.
create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period
View 13 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 21, 2013
I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.
eg
id description
78664 Pumps Alarm from CAMS RTU154
In the above example this column has 5 word so i want data in 5 rows like below
78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154
This column data can be varied from 1 to any number of words.
View 11 Replies
View Related
Jun 10, 2013
I do have a query which gives me two rows of data.(This might be more than two in other cases)
The data I get is formed as :
COUNTRY_DESCRIPTION COUNTRY_CODE EVENT_NUMBER EVENT_DATE APPLICANT
European Patent EP 101 Kiksu
European Patent EP 101 Mokilosu
What I want to get should be formed as :
COUNTRY_DESCRIPTION COUNTRY_CODE EVENT_NUMBER EVENT_DATE APPLICANT
European Patent EP 101 Kiksu
Mokilosu
As you can see, I removed the first four columns because the eventkey is the same. In this case, there is only the applicant which is different.So the rest should be blank.
The code I do use to get this data:
SELECT
TABLECOUNTRY.COUNTRYDESCRIPTION "COUNTRY_DESCRIPTION"
,TABLECOUNTRY.COUNTRYCODE "COUNTRY_CODE"
,OWNER.NAME "APPLICANT"
,CASEEVENT.EVENTNUMBER "EVENT_NUMBER"
,TO_CHAR(CASEEVENT.EVENTDATE,'DD.MM.YYYY') "EVENT_DATE"
[code]....
So what to modify in the query to get these columns of the second row blank?
View 17 Replies
View Related
Jul 31, 2013
I have a INSERT query which is happening with a SELECT query.
===================================================
INSERT INTO tbl_fact_effort_lvl_data ( ............... )
SELECT ria.report_id,report_status: :,
((SELECT lov_num_val
FROM tbl_reference_data
WHERE lov_type = 'FREQUENCY' ) * (SELECT SUM(pph_task)
FROM tbl_ri_process
WHERE report_id = ria.report_id )) TOT_YEARLY_PROD_HOURS ,TOT_YEARLY_PROD_HOURS * tf.fac_value TOT_FACT_DATA,location_id
FROM tbl_fact tf LEFT JOIN ......... ;
====================================================
So, here I want to use column alias TOT_YEARLY_PROD_HOURS as another column to derive another column value TOT_FACT_DATA.
View 8 Replies
View Related
Aug 9, 2011
I have a table like below-
create table test (cust_id number(3), m_no number(1), other_date varchar2(20));
Where there is a "cust_id" that can have maximum 3 "m_no" (Don't bother for that condition, we have it in actual table).
In this table records are as follows.
insert into test values (1,1,'ABC');
insert into test values (1,2,'DEF');
insert into test values (2,1,'DEG');
insert into test values (3,2,'DEG');
[Code]..
My objective is to get date like below--
cust_idf_m_nof_oth_datas_m_nos_oth_datat_m_not_oth_data
1 1 ABC 2 DEF
2 1 DEG
3 2 DEG 1 BCD 3 MNO
4 9 KLM
5 2 XYZ 5 XAZ
I want to say, that cust_id's that doesn't have second and third m_no should not have any data in those columns.
Can this be performed through single query, or I need to make temporary tables or functions for this.
View 7 Replies
View Related
Jan 11, 2012
I have a column which holds the data in the below format.
Source Data :
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192>SNO_54-90-16489-008G
>SNO_SDPMDB-008G-11>SNO_54-90-18008-008G>SNO_54-62-08791-008G>SNO_20-81-00327
>SNO_SDPMDB-008G-12>SNO_54-90-17830-008G>SNO_54-62-08598-008G>SNO_20-81-00327
[code]..
Problem Statement :
Split the above data into individual components and create columns / aliases dynamically.If the column is present then place the data under the created column.
Example 1) :
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192
Result
Column Name : SKUCol_54-99
Data :SNO_SDSDQ-8192-BNSNO_54-99-24120-8192
Example 2)
>SNO_SDSDAA-002G-101-J>SNO_54-90-16002-002G>SNO_54-62-05781-002G>SNO_20-81-00135-5
Column Name : SKU54-90Col_54_62Col_20_81
Data :SNO_SDSDAA-002G-101-JSNO_54-90-16002-002GSNO_54-62-05781-002GSNO_20-81-00135-5
Column Name can be derieved from the components like SNO_54-62-05781-002G
i.e. SNO_54-62-05781-002G ==> SNO_-05781-002G
"54-62" will give the Col_Name
The First Column will always have data starting with
SNO_SD%
This column is constant and will be named as SKU.
I need this data to be placed in the same record / row but under different columns as per the data set.Basically, Can the data be split into multiple parts based on delimeters and the columns are created based on the unique data in the parts that form the data in the column.
View 1 Replies
View Related
Feb 28, 2013
I have string like 'PRASAD,ALLEN,STEWART,SMITH'.
LIKE
COL1 COL2 COL3 COL4
-------------------------------
PRASAD ALLEN STEWART SMITH
I want to store the data into columns using SELECT statement only
View 7 Replies
View Related
May 10, 2013
when we have a primary key on 4 columns and we have, say 20 million rows and we want to add one extra row. How does oracle check whether the data on the primary key is unique to the record being added compared to the 20 million rows. Does it actually compare the record being added to all the rows present in the table?
View 2 Replies
View Related
Jul 19, 2013
I'm new to Oracle Forms 10g.
When I create a data block based on a table using Data Block Wizard, the block's Query Data Source Columns property is automatically populated with column definition entries corresponding to the columns of the base table.
I tried making changes to these entries, for example by changing the data types to wrong data types or even deleting them, and I found that those changes had no effect on the block at all. The form was still working as I wanted.
What is exactly the role of the block's Query Data Source Columns property.
The F1 key help says "The Query Data Source Columns property is valid only when the Query Data Source Type property is set to Table, Sub-query, or Procedure". So, explain in each context of Query Data Source Type.
View 2 Replies
View Related
Oct 22, 2012
query to find out common data from 2 columns in two different tables??
View 6 Replies
View Related
Mar 11, 2013
I have a Employee table of the following format:
Emp_id | Emp_name | Salary
101 | James | 1000
102 | Julia | 200
I have to convert or transpose the table data as follows using a SQL statement/function -
Emp_id | 101 | 102
Emp_name | James | Julia
Salary | 1000 | 2000
How do I achieve this?
View 4 Replies
View Related
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
Feb 4, 2013
CREATE TABLE FEB05
(NAME VARCHAR2(20),
CITY VARCHAR2(20))
INSERT INTO FEB05 VALUES ('AKON','CIT22')
INSERT INTO FEB05 VALUES ('MARIA','WOT101');
[code]......
I WANT A QUERY THAT WILL GIVE O/P AS TO GET DISTINCT COUNT OF NAME AND CITY COLUMN separately.
I.E. THE O/P SHOULD BE
Quote:Count(dist name) Count(dist city)
6 5
View 5 Replies
View Related
