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.
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 )
WITH table1 AS -- this table contain a list of column names ( SELECT 11 cid, 'TEK' group_nm, 'TYPE DESC' column_nm FROM dual UNION ALL
[Code].....
i have 3 tables, one that contains name of columns(table1), another one contain number of rows (table2) and the last table contain
the values for columns in table1 for each row in table2.
what i want to do is join all 3 tables and display the output as follow
TYPE DESC NAME P DATE TYPE_PCT ==================================================== Good John 8/21/2010 0.009 BAD Ken 4/11/2010 10.009 Medium Rob 8/1/2010 0.001
as you can see the columns names comes from table1, and the values comes from table3. i want to join these tables so that it display the output above
SELECT LOC_CD, TO_CHAR(DT,'fmMon RRRR'), SUM(QTY) , GROUPING_ID(LOC_CD) FROM WIP WHERE LOC_CD IN (1,2,3,4) GROUP BY ROLLUP(LOC_CD), TO_CHAR(DT,'fmMon RRRR'),TRUNC(DT,'MM') ORDER BY TRUNC(DT,'MM'),LOC_CD
This query result attached
The red coln is the total I want to place it in row-wise
Date loc_1 loc_2 loc_3 loc_4 Total May 2012 4,554 6,644 11,198 June 2012 4,986 5,838 777 11,601 22,799
INSERT INTO CFL VALUES (11, 1, 'JAN-10', 10); INSERT INTO CFL VALUES (21, 1, 'FEB-10', 20); INSERT INTO CFL VALUES (31, 1, 'MAR-10', 10); and so on (12 records for a year with same quota_id, e.g. here it is 1) [code].......
Also this column values period is dynamic.. it can be for any year.
The select will return values like
Select per_id, PERIOD, amount from cfl where quota_id = 1
Basically 12 rows will be the output: per_id period amount 1 JAN-10 10 1 FEB-10 20 1 MAR-10 10 ..............and so on
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
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.
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...
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,
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));
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>
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.
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
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
create type emp_obj_dtl as OBJECT (ename varchar2(50),mgr NUMBER) create type emp_dtl_obj_typ as TABLE of emp_obj_dtl
Using the these object i have created on function as
CREATE OR REPLACE FUNCTION emp_test_func (peno NUMBER) RETURN emp_dtl_obj_typ AS lv_emp_dtl emp_dtl_obj_typ := emp_dtl_obj_typ (); BEGIN SELECT emp_dtl_obj_typ(emp_obj_dtl (ename, mgr)) INTO lv_emp_dtl FROM emp WHERE empno = peno;
RETURN lv_emp_dtl; END;
Now if i am executing query as
SELECT empno, emp_test_func (empno) emp_dtls FROM emp
In this query split is a pipe line function to convert row(rows stored with , delimited) as columns like below
for ex for below query SELECT * from TABLE(SPLIT('bbb003,bb004'));
out put is bbb003 bb004
now i have to apply same function on column,column is storing data with ',' separated.and i have tried like but it's throwing missing expression. how i can use this function on entire column from this table.
SELECT * from TABLE(SPLIT(select candidates FROM ibis.cw_uploads_inprogress ));