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
I have a table which stores Employees and their Phone numbers. Each employee can have multiple numbers e.g.
Employee, Number
Adam, 123
Adam, 456
John, 123
John, 456
I am trying to write a select statement that will split the rows into columns and group by each employee e.g.
Employee, Number1, Number 2
Adam, 123, 456
John, 123, 456
I have a table that has about 20,000 rows.
There is a column called Keyword which has values like below:
File_IDKeyword1SMITH;ALLEN;WARD;JONES; BRADY2S&P500;TOPIX3SMALL;LARGE;MEDIUM
I want to output the data like this: FILE_IDKEYWORD1SMITH1ALLEN1WARD1BRADY2S&P5002TOPIXetc
I'm using this query and it works: SELECT STG.FILE_ID, REGEXP_SUBSTR(STG.KEYWORD,'[^;]+', 1, LEVEL) AS KEYWORD FROM STG_TABLE STGCONNECT BY REGEXP_SUBSTR(STG.KEYWORD,'[^;]+', 1, LEVEL) IS NOT NULL
But its sooooo slow, its unusable. Is there a quicker way to return this output? Other info:KEYWORD is varchar2(4000) but rarely more than 100 bytes are usedOracle 11g2 !
I have a table like this:
ID1 ID2 Ini_date End_date
1 1 2008-05-14 2010-09-16
1 2 2010-01-21 2010-08-26
..... ..... ............. ...................
and I would like to have a row for each year between ini_date and end_date.
ID1 ID2 YEAR
1 1 2008
1 1 2009
1 1 2010
1 2 2010
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
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
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
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...
column1 column2 column3 column4
12 Mar-21-2005 BDW blah blah blah
11 Feb-07-2001 ZV ha ha ha
12 Jan-02-2002 YM zuck zuck zuckI want a view that has that data like this:
column1 column2
12 Mar-21-2005 - BDW - blah blah blah; Jan-02-2002 - YM - zuck zuck zuck
11 Feb-07-2001 ZV ha ha haCan you help with SQL ?
I tried to use this Oracle LISTAGG function in the SQL, but got a "string concatenation limit exceeded"
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.
I have a small requirement...
Create table temp_a (source_code varchar2(100), target_code varchar2(1000));
Insert into temp_a values ('1','002.0 AND 002.9');
Insert into temp_a values ('2','729.90 AND 079.99 AND 002.9');
Output :
1 002.0
1 002.9
2 729.90
2 079.99
2 002.9
So, once we get the output, it needs to be joined to another table. I did Google search, but most of them are retuning collections / arrays as output. Not sure how I join the collection with the table.
create or replace function splits
(
p_list varchar2,
p_del varchar2
) return split_tbl pipelined
is
l_idx pls_integer;
[code].......
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 ));
SELECT 'TEST','F1,F2,F3,F4' from dual
I want to split the rows by comma separated as below
TEST F1
TEST F2
TEST F3
TEST F4
When referencing a procedure during a trigger, can I split the parameters across multiple lines? Similar to a backslash in perl? I've written a simple send mail procedure and it works well, though the parameter list is large and I'd like to be able to format the code for readability, i.e.:
BEGIN
send_mail('from@domain.com', 'to@domain.com, ???
'Subject', 'Message');
END;
What would I replace ??? with to extend the procedure to the next line?
I know this sounds like a very elementary question, but I've yet to figure it out via queries on these forums or Google. Perhaps I'm not choosing the right words.
When creating the procedure, i was able to use || to extend the utl_smtp function parameters, but I get an error when using the same syntax during trigger creation.
Scenario 1 Query should check for priority record(25), if the start_date and end_date of that priority record is the max in that group, records will not have any split.output will be the same.
DC Store St Date End date Priority
955 3 1/1/2010 12/31/9999 25
966 3 4/5/2011 10/10/2011 50
977 3 10/12/2011 12/12/2012 100
output
DC store St Date End date Priority Rank
955 3 1/1/2010 12/31/9999 25 1
966 3 4/5/2011 10/10/2011 50 2
977 3 10/12/2011 12/12/2012 100 3
Scenario 2 If priority record is not covering the max range, then split the records as shown below,
1. during the time period 1/1/2011 & 4/30/2011 there were no other DC for that store so rank would be 1
2. the next range would be 5/1/2011 to 6/29/2011 we have 2 records in service so the record with low priortiy would be ranked 1 and second priority would be ranked 2
3. similarly, for 6/30/2011 to 10/1/2011 we have 3 records in service and it will be ranked accordingly on the priority.
DC Store St Date End date Priority
966 3 6/30/2011 10/1/2011 25
955 3 5/1/2011 11/30/2011 50
977 3 1/1/2011 12/31/2011 100
output
DC store St Date End date Priority Rank
977 3 1/1/2011 4/30/2011 100 1
955 3 5/1/2011 6/29/2011 50 1
977 3 5/1/2011 6/29/2011 100 2
[code]....
Scenario 3 This works similar to scenario 2
DC Store St Date End date Priority
966 3 2/1/2011 12/31/2011 25
955 3 1/1/2011 12/31/2012 50
977 3 5/1/2011 06/31/2011 100
output
DC store St Date End date Priority Rank
955 3 1/1/2011 1/31/2011 50 1
966 3 2/1/2011 12/31/2011 25 1
955 3 2/1/2011 12/31/2011 50 2
977 3 5/1/2011 6/30/2011 100 3
955 3 1/1/2012 12/31/2012 50 1
Note: Number of records in the input can vary and ther can be duplicates in the date interval
I have a query that produces around 11 fields, and one of which is a multi-delimited field and the other 10 are dimension fields. I would like to split that field into several rows, and have the other 10 fields just repeated for each one. Here is an example of the data in the 11th field :
Column 11
34^56^78,59
There are two delimiters in the field, a carat and a comma. This field is used to reference document numbers that are needed to be sent in. The carat represents the word "Or" and the comma represents the word "And". I would like to have the output of each field to be a repeat of the 10 dimension fields, plus 3 new fields. The first new field would be the document number, the second new field would be the position within the original delimited field(1, 2, 3, etc.) , and the last field would be one of three logic words :First (if it is the first value), Or (if the value followed a carat), And (If the value followed a comma). Example of the output from the above value would be :
Column 11 Column 12 Column 13
34_______ 1_______ First
56_______ 2_______ Or
78_______ 3_______ Or
59_______ 4_______ And
Any thoughts on this? I have found a few solutions online on how to break up the delimited field into rows, but never with multiple delimiters or with extra logic for the added fields.
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 Want to make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.
View 2 Replies View RelatedDB Used : Oracle 10g.
A table X : NUM, INST are column names
NUM ----- INST
1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1
-- Based upon requirement :
1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:
For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298
3) Save these values into
A table Y : INST, NUM are column names.
INST NUM
23 1234,1298
1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.
2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..
I have a table TableA containing 2 columns ( Name and Value). Here I know what are the values for column Name
TABLEA
=======
Name Parameter
-------------------------
Nexus 11
GPlay 21
Demo 31
I need a query which provides the below output
Desired Output:
======
First Second Third
11 21 31
I have tried the below query
SELECT
DECODE (name,'Nexus', parameter) First,
DECODE (name, 'GPlay', parameter) Second,
DECODE (name, 'Demo', parameter) Third
FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));
This gives me the output
First Second Third
11 <Empty> <empty>
<empty> 21 <empty?>
<empty?> <empty?> 31
Is there any way to get the output in single line.
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
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
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 ?
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.
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.
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 Relatedselect * 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)
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
Can i select multiple columns while using group by single column?
View 5 Replies View RelatedGetting 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].........