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
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
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.
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 need a single select query which converts all the rows into a single value . Below is my requirement :
Create table email_tbl(emailid varchar2(30)); insert into email_tbl('1@y.com'); insert into email_tbl('2@y.com'); insert into email_tbl('3@y.com'); insert into email_tbl('4@y.com');
Now , I need a single select query which gives me the below results.
1@y.com,2@y.com,3@y.com,4@y.com
I have done the above by using a cursors in the pl/sql objects.But want to achieve this with a single sql/query.
I have one issue with the converting columns into rows.
Employee table has manager , additional manager and some other columns. If the employee has additional manager, then i need those employee record with 2 rows : 1st row - manager with other data , 2nd row - additonal manager with other data .
Orginal data is like this:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ADDTIONAL_MGR ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7698
[code]....
I have tried this , using UNION ALL method, But it is taking more time.
SQL> ED Wrote file afiedt.buf 1 SELECT DECODE (DUMMYTAB.NUM,'1',MGR, '2',addtional_mgr) MANAGER, empno, ename, job, hiredate, sal, comm, deptno 2 FROM emp E, (SELECT '1' NUM FROM DUAL UNION ALL SELECT '2' NUM FROM DUAL) DUMMYTAB 3* WHERE addtional_mgr IS NOT NULL SQL> /
CREATE TABLE group_device ( group_id NUMBER (8) NOT NULL , member_id NUMBER (4) NOT NULL , device_id NUMBER (10) NOT NULL , install_date DATE NOT NULL , remove_date DATE );
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.
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>
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
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 ));
