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


ADVERTISEMENT

SQL & PL/SQL :: Select Statement That Will Split Rows Into Columns?

Aug 15, 2013

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

View 19 Replies View Related

PL/SQL :: Split Delimited Cell Into Multiple Rows

Aug 6, 2013

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 !

View 5 Replies View Related

SQL & PL/SQL :: Split Single Row Into Multiple Rows Containing Time Periods Per Year

Nov 26, 2010

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

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

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

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

PL/SQL :: Concatenating Columns (in Multiple Rows) To One Column In Single Row

Nov 28, 2012

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"

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

SQL & PL/SQL :: Split Csv To Multiple Records

Jun 18, 2013

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

View 3 Replies View Related

SQL & PL/SQL :: Split Is A Pipe Line Function To Convert Row As Columns

Jan 31, 2013

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

View 7 Replies View Related

SQL & PL/SQL :: To Split The Rows By Comma Separated

Mar 4, 2011

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

View 5 Replies View Related

Split Procedure Parameters Over Multiple Lines

Apr 4, 2007

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.

View 3 Replies View Related

SQL & PL/SQL :: Multiple Record Split On Date Conditions?

May 12, 2012

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

View 5 Replies View Related

SQL & PL/SQL :: How To Split Multi-delimited Field Into Several Rows And Add New Fields

Mar 7, 2013

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.

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

SQL & PL/SQL :: Order By Field Containing Characters And Numbers (split From Convert No Rows Returned To Zero)

Jan 13, 2012

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 Related

Performance Tuning :: Split Data Separated By Comma / Then Create Collection With Data Mapped From Other Columns

Sep 25, 2013

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

View 4 Replies View Related

PL/SQL :: Multiple Rows To Multiple Column

May 6, 2013

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.

View 3 Replies View Related

SQL & PL/SQL :: Rows To Columns?

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

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

Converting Rows To Columns Using OWB

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







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