SQL & PL/SQL :: Query To Transpose Quest_id And Ans_id Columns Into Rows For Matching Records
Nov 16, 2012
create table ref_tbl (col1 varchar2(10),
col2 varchar2(10),
QUEST_ID_1 varchar2(10),
ANS_ID_1 varchar2(10),
QUEST_ID_2 varchar2(10),
ANS_ID_2 varchar2(10)
);
[code]...
Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records...my output should be like
CUS_IDCOL1COL2QUEST_IDANS_ID
------------------------------------------------
1PMP1234Q101A1234
1PMP1234Q102A2345
1STR2345Q201A2234
2PMP1234Q101A1234
2PMP1234Q102A2345
2STR2345Q201A2234
View 11 Replies
ADVERTISEMENT
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
Jul 15, 2013
I have a result table from a select query. Create_table.txt
FDAVAILABILITY_2GAVAILABILITY_3GTCH_TRAFFICHOSRCSSR
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
TCH_TRAFFIC28359.094328512.486528659.325628355.0263
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
Jul 3, 2012
I need to display the record when all the columns have matching records,If one of them doesn't match then it should not be displayed
The following is the example
WITH t1 as
(select 159435 ky from dual)
,t3 as
(select 78 id ,'Z-' rk,'SL' cd from dual union all
select 78 id ,'Z+' rk,'SL' cd from dual union all
select 78 id ,'Z-' rk,'SL' cd from dual union all
[code].....
In the above data bg.rk= 'Z-' but one of the record in T3 is having Z+ ,So this should not be displayed (same condition with column CD) in this example cd column in both table matches I tried like above query but i'm getting the record.
View 8 Replies
View Related
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
Aug 6, 2013
I have a requirement to transpose the column to rows like the one below, i am not getting the logic on how to do it, If any of you have come across this logic,
Existing Table:ABCD1234
Hypothetical table: ColumnValueA1B2C3D4
View 2 Replies
View Related
Aug 10, 2010
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> /
View 15 Replies
View Related
Jul 27, 2012
I have following table -
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
);
[Code]...
Current output does looks like below
10 1 123 23-MAY-12 28-MAY-12
10 1 456 28-MAY-12 01-JUN-12
10 1 789 01-JUN-12
10 2 999 04-MAY-12 17-MAY-12
10 4 1123 22-JAN-12 27-JAN-12
10 4 1456 27-JAN-12 28-JAN-12
Device_id - Replaced_device_id - remove_date :: For group_id=10
e.g. for member_id = 1, device_id=123 got replaced by device_id=456 on 28-May-12
device_id=456 got replaced by device_id=789 on 01-Jun-12
So output should look like this
10 123 456 28-May-12 -- Replaced
10 456 789 01-Jun-12 -- Replaced
10 789 -- Active
Similarly there will be many groups, so it should list based on group_id
View 8 Replies
View Related
Jul 26, 2013
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 -
SELECT * FROM T2;
C BATOT LY_BATOT LLY_BATOT BSCTOT LY_BSCTOT LLY_BSCTOT BAMSTOT LY_BAMSTOT LLY_BAMSTO
- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
5000 2000 12600 20000 45600 35000 45000 56000 65000
[Code]....
View 7 Replies
View Related
Sep 14, 2008
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.
View 1 Replies
View Related
Jun 19, 2013
the query used is
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
View 1 Replies
View Related
Feb 19, 2013
Im using oracle 11g.
I have data in a table as below: Column names are type,month,percentage
TYPE MONTH PERCENTAGE
A1 JAN-2013 100
A2 JAN-2013 100
A3 JAN-2013 95
A4 JAN-2013 98
[Code]...
The desired output using a sql query is as below
TYPE JAN-2013 FEB-2013
A1 100 100
A2 100 99
A3 95 88
A4 98 67
A5 99 98
A6 90 95
A7 92 84
View 7 Replies
View Related
Oct 5, 2012
CREATE TABLE CFL (q_id Number(18),per_id number(18),PERIOD VARCHAR2(15 CHAR), AMOUNT NUMBER);
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
1 DEC-10 10
The result output should be:
Per_id JAN-10 FEB-10 MAR-10.............DEC-10
1 10 20 10................10
View 14 Replies
View Related
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
Mar 15, 2010
I have two table and trying to update tableA with data from tableB but gives an error. My TableA has columns - colA,colB,colC,colX and table B has columns - colA,colB,colX. Sample data looks like this:
tableA:
XXYY, local,3/19,48
XXYY,ixc,3/19,24
XXYYlocal,3/20,48
XXYY,local,3/21,48
Table B:
XXYY,local,48
XXYY,ixc,24
PPQQ,local,72
this is the query I wrote
update tableA a
set (a.colX)=(select (b.colX) from tableB b where a.colA=b.colA and a.colB=b.colB);
When i run it gives this error:single row subquery returns more than one row.
tableB has only 1 value for colX for each colA,colB record but tableA has mutiple colA and colB repeated but for all of thsoe in table A i want to update the tableB.colX value for matching colA and colB.
View 4 Replies
View Related
Aug 10, 2011
I have a personname table which contains records of millions Person-names. My application has a requirment to return "any" 200 names that match the given Firstname and lastname entered by user.note the NOT actually "top-n", but "Any-N" , i.e. user wants "any" 200 names and NOT in any "specific order".
which is the best option to make most efficient search --
1) rownum < 201
2) row_num()
3) rank/denserank etc
View 3 Replies
View Related
Dec 19, 2012
I have one table employee where there are 4 fields ,emp_code,emp_locn,emp_job_code,emp_job_desc the problem is i am trying to prepare a group reports based on location and emp_job_code there is a duplication of data in the emp_job_desc ,
For example there is a job_code E2 Which has two different job_descriptions for two different employees like E2-PAINTER-SPRAY, E2- PAINTER -SPRAY, another example is E1-rigger , E2-RIGGER and so on.Is there a method to match them together as one description.
View 7 Replies
View Related
Jul 17, 2012
write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies
View Related
Jun 30, 2010
For one example table t1 as below, I want to find the maximum 5 biggest size, but there should not be more than 2 name from the same position.
Name Position Size
N1 P1
N2 P2 50
N3 P3 500
N4 P1 6
N5 P2 60
N6 P3 600
N7 P1 7
N8 P2 70
N9 P3 700
View 1 Replies
View Related
Mar 9, 2011
Query : select * from scott.dept where deptno=10;
Result
DEPTNO |DNAME |LOCATION
-------|----------|----------
10 |ACCOUNTING|NEW YORK
But i want the above resultset as below,
COL1 COL2
----------------------
DEPTNO | 10
DNAME | ACCOUNTING
LOCATION | NEW YORK
View 4 Replies
View Related
Aug 21, 2013
I am using: Oracle SQL Developer (3.0.04) Build MAin-04.34 Oracle Database 11g Enterprise Edition 11.2.0.1.0 - 64bit Production Sample dataTable
with t as (
select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',101, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 106, 105, 0, 'origin', 150, 200 from dual union all
[code]..........
Is there a way to check in that date grouping for matching ticket_origin and ticket_destination when there may be two or more rows difference between them that does not allow me to use Lead or Lag function. Is it also possible do so without using the amount column? I also would like to identify if they are in the same area when paired (this I believe works after getting table sorted like so below then use lead lag after having the order by done) I am trying to get something like this table with results as
select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124.', '124._', 105, 0, 106, 'destination', 150, 300 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '127_', '127', 108, 108, 0, 'origin', 50, 600 from dual union all
[code]...........
View 12 Replies
View Related
May 15, 2012
i have two table menu_user_d and emp_master , the requirement is i need to match names from both these tables like i want want to match menu_user_d.user_desc to emp_master.emp_name to get the emp_id and update the correct emp_id into menu_user_d.user_emp_id , i have prepared a test case and wrote one query but could'nt succeed in getting all the matching names, is there a way i can do this correct matching and then update it automatically.
SQL> create table menu_user_d (user_id varchar2(12),user_desc varchar2(30),user_emp_id varchar2(12));
Table created.
SQL> insert all
2 into menu_user_d(user_id,user_desc,user_emp_id) values ('ARIF','Mohammed Arif',null)
3 into menu_user_d(user_id,user_desc,user_emp_id) values ('wajahat','Wajahat',null)
4 into menu_user_d(user_id,user_desc,user_emp_id) values ('Imad','Imd',null)
5 select * from dual;
3 rows created.
SQL> commit;
Commit complete.
SQL> create table emp_master (emp_id varchar2(12),emp_name varchar2(30));
Table created.
SQL> insert all
2 into emp_master(emp_id,emp_name) values ('7014','Md.Arif')
3 into emp_master(emp_id,emp_name) values ('6777','Wajahat')
4 into emp_master(emp_id,emp_name) values ('1008','Imad El Kane')
5 select * from dual;
3 rows created.
SQL> commit;
Commit complete.
SQL> SELECT USER_ID,USER_DESC,USER_emp_id,EMP_id,EMP_NAME
2 FROM MENU_USER_d,EMP_master
3 WHERE UPPER(USER_DESC) LIKE UPPER(EMP_NAME) ;
USER_ID USER_DESC USER_EMP_ID EMP_ID EMP_NAME
------------ ------------------------------ ------------ ------------ ------------------------------
wajahat Wajahat 6777 Wajahat
View 28 Replies
View Related
Aug 17, 2010
This is my query:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
View 9 Replies
View Related
Oct 1, 2012
I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.
HERE
select a.*
from DOC a , FOLDER B
where a.NUMBER= B.INCIDENT_ID
and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.
View 12 Replies
View Related
Jan 21, 2011
I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
View 5 Replies
View Related
Sep 6, 2010
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
Query for anyone interested:
Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.
View 18 Replies
View Related
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
Feb 8, 2013
I want to learn how to make columns out of records?
View 8 Replies
View Related
Oct 8, 2010
I need to compare records in the two columns (varchar). I need to find almost the same names from that columns.
For instance:
'MAPE STUD' <-> MAPE STUDY
And I should see that these records are the same because different is only in the one word.
View 31 Replies
View Related
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
