Summarizing Over Multiple Columns Aggregated On Grouping Field

Aug 9, 2011

I have spent the past hours trying to come up with a query to get the following starting table to the required result table.

STARTING TABLE:

PCD TEL

A 1
A 2
A 3
A 4
A 5
B 2
C 3
C 4

REQUIRED RESULT:

PCD TEL1 TEL2 TEL3 TEL4

A 1 2 3 4
B2 NULL NULL NULL
C 3 4 NULL NULL

View 1 Replies


ADVERTISEMENT

Reports & Discoverer :: Create A Report By Using One Field / Text As Columns Name In Layout But Display All Columns

Jun 16, 2010

I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.

View 4 Replies View Related

PL/SQL :: Aggregated Functions Moving Value One Row Down

Feb 7, 2013

this query

SELECT
      shrtgpa_pidm,
    shrtgpa_term_code,
    sum(shrtgpa_hours_earned) over (
   partition by shrtgpa_pidm
   order by shrtgpa_term_code

[Code]....

retrurns this rsult

82724     201010     4.5     201010
82724     201020     9     201020
82724     201110     13.5     201110
82724     201120     17.5     201120
82724     201310     20.5     201310

I need to be able to displayu the 4.5 in 201020 9 in 201110 in another words I need to move the results one term down

how I can do this?

I am thinking in a sequence? so 201010 will be seq 1

201020 will be seq 2
201110 will be seq 3
etc..etc..the I can do an update

Can this be done with aggr sql func? If not how I can add a sequence in the query It needs to break by shrtgpa_pidm

I need to end with these results
201020    4.50
201110    9.0
201120   13.50
201210    17.50
201220    21.50       

here is the code to withe a table with data

create table testgpa
(
  testgpa_pidm                 NUMBER(8)            NOT NULL,
  testgpa_term_code        VARCHAR2(6 CHAR)     NOT NULL,
  testgpa_hours_earned    NUMBER(9,3)          NOT NULL
  )
 
[Code]....

View 2 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

SQL & PL/SQL :: Multiple Rows On A Table To Multiple Columns On One Row

Nov 26, 2010

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

View 6 Replies View Related

SQL & PL/SQL :: Multiple Field Update

Jun 11, 2011

I have a requirement to load data into database column based on the value coming from input field.

E.g. :

Input:

EmpNo, DeptNo, Sal
1234, 10,1000
1234,20,2000
1234,30, 3000
1234,40,4000
2345,10,100
2345,20,200
2345,30,300

Output:

Emp No, Dept_10_Sal,Dept_20_Sal,Dept_30_Sal,Dept_40_Sal
1234, 1000, 2000,3000,4000
2345,100, 200, 300,0

Here we have more than 500 columns like this Dept_10_Sal, Dept_20_Sal........Dept_500_Sal.

View 32 Replies View Related

PL/SQL :: Parse Multiple Items In A Field

Apr 1, 2013

I have a field that contains many part numbers. There can be 2 or more part numbers in the field separated by a space. How can this be dynamically read so you can compare the contents in the where clause to what you are search for? Example Part Numbers in the field: 1X 2X 3X 4Y 5Y 7Y

I would like to read the field and make rows out of the data.

1X
2X
3X
4Y
5Y
7Y

View 3 Replies View Related

SQL & PL/SQL :: Get Single Row From Multiple Duplicate Values Of One Field?

Sep 10, 2012

CREATE TABLE prim_tbl
(id NUMBER,--- id is not primary key here
description VARCHAR2(30));

INSERT ALL
INTO prim_tbl VALUES (1,'aad')
INTO prim_tbl VALUES (1,'aads')
INTO prim_tbl VALUES (2,'bb')
INTO prim_tbl VALUES (2,'cc')
INTO prim_tbl VALUES (2,'dd')
SELECT * FROM dual;

I want to select the ids only one time, i.e my output will have only two rows: one row with id as 1 and other row with id 2 whatever be the description.

desired output sample:

Quote:1, aad
2, bb

I used:
select distinct(id),description from prim_tbl;

but it did not give the required result.How can I get it??

View 6 Replies View Related

SQL & PL/SQL :: How To Update Multiple Columns

Dec 12, 2011

how to over come this error, because i need to update only 3 columns in the table and iam getting error when iam updating like this

update country1 set cname='japan','usa'
where cid=100,101

ERROR:ORA-01747,INVALID USER.TABLE.COLUMN,TABLE.COLUMN,OR COLUMN
SPECIFICATION

View 6 Replies View Related

SQL & PL/SQL :: Sort On Multiple Columns

Jun 16, 2010

how does sorting on multiple columns work

suppose my query is

select * from person order by first_name desc
and sys_person_id asc

this query works , but is this write way to sort on multiple column ?

View 12 Replies View Related

SQL & PL/SQL :: Order By Multiple Columns

Oct 9, 2013

I have a query to pull the first contact of students.

The table has all contacts like parent/guardian, friends family, emergency contact etc.

I would like to the first primary contact in this order,
1. initial contact, 2. same as student address and also have to be parents,
3. live with and also a parent, 4. parents 5. friends.

I don't know how to pull 2 and 3 . because it looks like it needs to concatenate the columns.

here is my initial query

Select Min(U2.Id) Keep (Dense_Rank First Order By U2.Initial_Contact Desc, U2.Same_As_Students_Address Desc,u2.lives_with DESC,U2.Guardian Desc)
From Contacts

how to achieve 2 and 3?

The table script is attached. All the above columns are 1 or 0.

View 3 Replies View Related

SQL & PL/SQL :: Multiple Queries Into Different Columns?

Jan 31, 2013

I am trying to validate a monthly report so was trying to write queries to get different criteria into one table. So my first query returns all the product,second query returns all the enrolled customers, 3rd query returns all the cancelled customers and 4th query returns all the newly enrolled for a month. Is there a way I can pass the first query results into 1st column, 2 query results into 2nd column, 3 query results into 3rd column and so on.

I tired writing the SQL several different ways and have spent a day on it and still cannot figure it out. I am using SQL Developer.

View 9 Replies View Related

SQL & PL/SQL :: Top Row Based On Multiple Columns?

Dec 1, 2011

I have a table with Column A, B, C. I want to write a query to retrieve the top row of A, B combination. i.e, for every unique value of A,B combination I want the row having highest value for C. I tried using rank() function but am not able to get the top row with combination of A,B.

View 8 Replies View Related

PL/SQL :: Multiple Columns In IN Clause?

Jun 19, 2013

select * from nrc_trans_descr where type_id_nrc=60013   -- it has 18 columns and i have hard coded 60013 for simplification here.60013 is derived from 3 other table Output is ( it can have many rows too.typically for each type_id_nrc there is one row ).
 
TYPE_ID_NRC  TRIGGER_STATUS  INSTALLMENT_TYPE_ID_NRC
---------------------------------------------------------------------
      60013              0                 61013                  
i have to pass TYPE_ID_NRC  and  INSTALLMENT_TYPE_ID_NRC to restriction_id column in a different table. currently i am doing like this 
select * FROM DISCOUNT_RESTRICTIONS WHERE discount_id in (12085,12086)
and (restricted_id in (  select type_id_nrc from nrc_trans_descr where type_id_nrc=60013)
or restricted_id in (  select installment_type_id_nrc from nrc_trans_descr where type_id_nrc=60013));

am using ORACLE 10GR2(solution for 11gr2 is welcome too) 

View 16 Replies View Related

SQL & PL/SQL :: Query With Multiple Columns

Dec 17, 2012

Table-Name

ID Status description Tracking ID
1 Strat Frog 1
2 Start Dog 2
3 Process Frog 1
4 Completed Dog 2
5 Start Rabbit 3
6 Error Frog 1
7 Stop Rabbit 3
8 Start Elephant 4
9 process Elephant 4
10 Start Human 5
11 Stop Human 5
12 Start Butterfly 6
13 completed Butterfly 6
14 start lion 7
15 error lion 8
16 complted lion 8
17 start tiger 9
18 error tiger 9

select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss')
And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));

Result should be " Frog and Elephant and tiger"

Start of every record(descrpition --status is Start)
End of every record ( status is stop or done or completed)
status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )

Note:

tracking IDs may change up on error

View 2 Replies View Related

PL/SQL :: Multiple Columns Using Group By

May 20, 2013

Can i select multiple columns while using group by single column?

View 5 Replies View Related

SELECT DISTINCT On Multiple Columns

Sep 17, 2010

I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:

select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob
from patients join schedule using (key_patien)
join adtmirro using (key_patien)
where appt_state = 'ON HOLD'

Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.

View 3 Replies View Related

Convert Rows To Multiple Columns

Aug 30, 2004

I have a table called N1

N1_no Srvarea_type_cd
1 P
1 P
2 C
2 C
2 C
3 I

Another table N2

N1_no srvarea_txt
1 ABCD
2 DEFG
3 XYZA

Can i get a query so that the data can be displayed in the following way ..

P C I
ABCD DEFG XYZA

View 3 Replies View Related

Multiple Columns Right To Left Orientation?

Sep 15, 2009

i have downloaded ireports latest version (3.6.0) for working with jasper reports (3.5.2) and creating reports;How can I change the orientation of a 4 columns report in order to generate the columns starting from the right side.I need to generate a multiple columns report in arabic and it should be done from right to left.

View 2 Replies View Related

Sub Partition By List On Multiple Columns

May 16, 2011

Can i partition by list on multiple columns? i am trying the following code, and it is returning an error. Is there a way round this?

CREATE TABLE
(...)
PARTITION BY LIST (col1)
SUBPARTITION BY LIST (col2)
SUBPARTITION TEMPLATE (
SUBPARTITION DETAIL VALUES ('DETAIL')
SUBPARTITION ROLLUP VALUES ('ROLLUP'))
(
PARTITION RT VALUES('RT')
)

View 1 Replies View Related

SQL & PL/SQL :: Using Cursor To Update Multiple Columns

Oct 22, 2011

I need to write a script which copies 4 col data from one table to another table. there are three tables

cwat_curr_mst and cwat_assigned_customer and cwat_assignment_mst.
Cwat curr mst has PK curr_id and cwat_assigned_customer has PK assignment_id.
Also cwat_assigned_customer has customer_id.
In cwat_assignment_mst has Curr_id and Assignment_ID.

cwat_curr_mst and cwat_assigned_customer tables has 4 cols in common
they are
ASRT_SNM_NO, SNM_NO, FLORIDA_NO, CBRN_NO.

So from curr_mst all these 4 cols data needs to come/copy into cwat_assigned_customer.

View 20 Replies View Related

SQL & PL/SQL :: Formatting Output Into Multiple Columns?

Apr 16, 2013

as part of an exercise we are to demonstrate a FOR LOOP from 1 - 100.

We must show the output as evidence of doing the work.

Is it possible to display the results in two columns, instead of all in one row? Because 100 rows will not fit it one screenshot!

BEGIN
FOR i in 1..100
LOOP
DBMS_OUTPUT.put_line(i);
END LOOP;
END;
/

View 5 Replies View Related

SQL & PL/SQL :: Groups With Multiple Columns And Links

Nov 29, 2010

the thread title was a bit confusing, couldn't come up with anything short to describe the question. What I am looking for is a query which will put records into groups based on matching values in one of two columns. So if two records have a matching value in column 1 or column 2 they are in the same group. See the example bellow and expected output for a "better" explanation:

--setup
CREATE TABLE foo
(foo_id NUMBER NOT NULL PRIMARY KEY,
record_number NUMBER,
record_value VARCHAR2(1));

[Code]...

--expected output

group# foo_id record_number record_value
1 1 1 A
1 2 1 B
1 3 2 B
1 4 2 C
2 5 3 D
3 6 4 E
3 7 5 E

My initial thought is that is feels a little bit like the sequential seat problem but not quite close enough. I know it could be done iteratively with PL/SQL but I am thinking there must be a way to do it in SQL I am not seeing yet.

View 6 Replies View Related

SQL & PL/SQL :: UNION ALL Output In Multiple Columns?

Nov 26, 2012

Following is my table structure:

14:54:17 PYMTPRODIAT@UCS43 > select * from DGTEST;

ID AMOUNT DI
---------- ---------- --
1 50 D
2 50 D
3 20 D
4 60 C
2 60 C
3 20 C

now, I want the output in the following format.what could be the easiest way.. I need the output in one query,,

ID D_Net_Amount C_Net_Amount
--- ------------- --------------
1 50
2 50 60
3 20 20
4 60

View 2 Replies View Related

SQL & PL/SQL :: Split Multiple Columns Into Rows

May 2, 2012

I have data like :

ID NAME1 NAME2 NAME3
JJ AD MED
VI TIBO PH TIBO

I want output like

ID NAME
JJ AD
JJ MED
VI TIBO
VI PH
VI TIBO

View 4 Replies View Related

PL/SQL :: Multiple Indexes Using Same Columns On One Table?

Oct 4, 2012

I have a table like MyTab(a int, b int), and I am required to create a primary key index and a non-unique index on this table using columns (a,b) in a specific table space.

The back end database is Oracle 10g.

Here's what I have tried so far, needless to say, unsuccessfully.

Alter Table MyTab
Add Constraint c_1 primary key (a, b)
Using Index (Create index mytab_idx on MyTab(a, b))
Using index tablespace results_index

So my question are:

1. is this is possible? if so, what is the correct syntax.
2. assuming it is possible, using this sort of construct before? it appears to be conflicting and inconsistent to me.

View 4 Replies View Related

Group By Multiple Columns / Count And Then Find A Max

May 24, 2008

I have three tables,let's say

table stores
sid | store_name
1 | one
2 | two
3 | three

table products
pid | sid | p_name
1 | 2 | pone
2 | 2 | ptwo
3 | 3 | pthree

table sales
said | sid | pid
1 | 2 | 1
2 | 3 | 1
3 | 2 | 2
4 | 1 | 3
5 | 2 | 2
6 | 3 | 2
7 | 3 | 2

and i want display the product that sells best in every store. I try to group by multiple columns counting how many times each product was sold in every store, but don't know how to select the one which was best sold (maximal number of times)

View 5 Replies View Related

Forms :: How To Create A Table With Multiple Columns

Dec 27, 2011

to create a table using forms with multiple columns

View 7 Replies View Related

SQL & PL/SQL :: Adding Multiple Columns To Table Type?

Mar 1, 2013

I have requirement on table type. without using bulk or %rowtype is there any possible to create table type with two or more columns.

I got with single column,but I am unable to create with multiple columns.

DECLARE
TYPE T1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
T T1;
CNT NUMBER:=0;
BEGIN
FOR I IN 1..100 LOOP
CNT:=CNT+1;

[code]....

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







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