SQL & PL/SQL :: Construct Query To Fetch Different Rows From Same Table In Different Columns

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


ADVERTISEMENT

SQL & PL/SQL :: Describe Results / Define Output And Fetch Rows Of A Query

Jun 2, 2010

There are several stages for sql processing in 10g2 database concept document.The following stages are necessary for each type of statement processing:

■ Stage 1: Create a Cursor
■ Stage 2: Parse the Statement
■ Stage 5: Bind Any Variables
■ Stage 7: Run the Statement
■ Stage 9: Close the Cursor
Optionally, you can include another stage:
■ Stage 6: Parallelize the Statement

Queries (SELECTs) require several additional stages, as shown in Figure 241:

■ Stage 3: Describe Results of a Query
■ Stage 4: Define Output of a Query
■ Stage 8: Fetch Rows of a Query

Stage 3: Describe Results of a Query The describe stage is necessary only if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user. In this case, the describe stage determines the characteristics (datatypes, lengths, and names) of a query's result.

Stage 4: Define Output of a Query In the define stage for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. These variables are called define variables. Oracle performs datatype conversion if necessary.

I still don't understand what's Stage 3: Describe Results of a Query and Stage 4: Define Output of a Query.

View 2 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

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

PL/SQL :: Fetch 1 Lac Rows From Table

Dec 15, 2012

OS : widows 2003

Oracle 9i release 2

I need to Fetch 1 lakh rows from table (all rows) in fastest way.

How to do it?

View 6 Replies View Related

OO4O And Wizards :: How To Fetch Columns In Cursor Using FETCH

Nov 1, 2012

create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS

CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;

Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?

View 1 Replies View Related

SQL & PL/SQL :: Query To Get Columns Into Rows?

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

PL/SQL :: Query - Rows In Columns

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

Converting Query Rows Into Columns?

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

SQL & PL/SQL :: How To Convert Query Columns Into Rows

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

PL/SQL :: Sql Query To Convert Rows To Columns

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

JDeveloper, Java & XML :: Convert Some Of The Rows Into Columns Query

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

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

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

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

Convert Rows To Columns In A Table?

Apr 24, 2012

How do I convert rows in oracle table to columns by writing a query?

Here is my table.

EID QNo Qual YOP
101 1 B.Tech 2004
101 2 M.Tech 2006
102 1 B.Tech 2003
102 2 M.Tech 2005
102 3 MBA 2007

Now I want the above table in the following format......

EID Qual1 YOP1 Qual2 Yop2 Qual3 Yop3
101 B. Tech 2004 M.Tech 2006
102 B. Tech 2003 M.Tech 2005 MBA 2007

I have maximum of 8 Qualifications, how to achieve this in oracle.

View 3 Replies View Related

Displaying Rows Into Columns In A Table

Oct 20, 2012

I have table having below records.

empno ename deptno
101 a 10
102 b 20
103 c 10
104 d 20
105 e 30

Normal Output

deptno count(*)
-----------------
10 2
20 2
30 1

I want to display like this(rows into columns)
--------------------------------------------------------
Required Output
-------------
10 20 30
2 2 1

View 1 Replies View Related

Converting Rows Into Columns And Updating A Table?

Apr 26, 2012

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.

View 1 Replies View Related

SQL & PL/SQL :: Comparing Multiple Columns According To Rows From Same Table

Aug 25, 2011

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...

View 12 Replies View Related

SQL & PL/SQL :: Update Number Of Rows In Three Columns In Table?

Dec 13, 2011

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,

View 8 Replies View Related

How To Convert Table Data From Rows To Columns

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

PL/SQL :: How To Convert Rows To Columns Data In A Table

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

SQL & PL/SQL :: Generate Columns Dynamically By Depending On The Rows In A Table In 11G?

Nov 27, 2012

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));

[code].....

View 4 Replies View Related

SQL & PL/SQL :: Update Column In A Table Which Has 3 Columns Of 16 Million Rows?

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

SQL & PL/SQL :: Fetch All Three Address Line With Max Length Of 40 In Each Columns

Jun 9, 2010

there are 3 columns as Address1(max data length 80) , Address2(max data length 80), Address3 (max data length 80)..I need to fetch all the three address line with max length of 40 in each columns after concatenate address1,address2,address3

E.g Field data_length
Address1 49
Address2 32
Address3 33

now I need to concate all the char and split in 40 char per field i.e. 49+32+33 = 114

Field data_length
Address1 40
Address2 40
Address3 24

View 15 Replies View Related

Query To Know Number Of Columns In A Table?

Apr 4, 2008

query to know number of columns in a table i.e.

if I want to know how many number of colums are present in a specific table then what would be the query.

View 1 Replies View Related

SQL & PL/SQL :: Transpose Rows-to-columns And Columns-to-rows

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

PL/SQL :: Oracle 9i - How To Construct Address

Apr 17, 2013

I'm using Oracle 9i. I want to make an address: city, state zip.

However, if there is no city or state, I don't want the comma. Doing this in decode made my head swim, so I went to case. I think that I mapped it out well, but I cannot have the variable on each section. This is my currently incorrect code.

CASE WHEN LENGTH(TRIM(AG.CITY)) = 0
THEN ST1.CODE || ' ' || AG.POSTAL_CODE
ELSE WHEN LENGTH(TRIM(ST1.CODE)) = 0
THEN AG.CITY || ' ' || AG.POSTAL_CODE
ELSE AG.CITY || ', ' || ST1.CODE || ' ' || AG.POSTAL_CODE
END ATTY_CITY_STATE_ZIPI'm trying to stick the end-product into ATTY_CITY_STATE_ZIP.

View 4 Replies View Related

SQL & PL/SQL :: Query For Columns In Table Including Constraints?

Jul 23, 2010

I want a query for all the columns in a table including there constraints and indexes of a "SCOTT" schema

View 7 Replies View Related

SQL & PL/SQL :: Fetch All Rows Into Single Variable

Feb 21, 2012

i want to load all rows of single columns into one variable. After that, that variable will use in any side of the pl sql block.

SET serveroutput ON
DECLARE
CURSOR CUR_DATA
IS
SELECT DAS_SECURITY_CODE FROM SECURITY_TYPE WHERE ASSET_TYPE='DERIVATIVES';
rec_data CUR_DATA%rowtype;

[Code]....

how to resolve this. above query,variable holds last value which fetched from cursor.

View 9 Replies View Related

PL/SQL :: String Search Query In Any Columns / Table In Database?

Sep 4, 2012

I am trying to search a word which starts with 'FRA' in any columns and any tables.

I am unable to find what is generating a join datasets in the webservice from teh database as it is not apparent within the 100 tables.

I ahve looked into

Re: How to search in all rows and all columns?
Re: SQL Search Query?

but none of these queries is working out for me as I am a user with no tables on its own but rather a user quering other tables.I think its a tweak on which dat a dictionary I can view

select distinct substr (table_name, 1, 14) "Table",
substr (t.column_value.getstringval (), 1, 50) "Column/Value"
from all_cons_columns,
table
(xmlsequence
(dbms_xmlgen.getxmltype ('select ' || column_name
|| ' from ' || table_name
|| ' where upper('
|| column_name
|| ') like upper(''%' || 'fra'
|| '%'')'
).extract ('ROWSET/ROW/*')
)
) t
order by "Table";

running teh above query got me thsi error:

ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause:    An error occurred when processing the XML function
*Action:   Check the given error message and fix the appropriate problem

View 4 Replies View Related

Select Query With Four Table Its Generating Around 650 Rows?

Feb 20, 2013

i have a select query with four table its generating around 650 rows. and i need to update these 650 rows only.

for example

update ps_po_lining b
set y.recv_req = 'N'
where recv_req in

[Code]....

this query runs but its updating 6000 rows. which is not right. we need to update what ever the select query is retrieving.

View 5 Replies View Related







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