SQL & PL/SQL :: Use Model Clause To Get Comma Separate Single Row For Multiple Rows?

Feb 19, 2010

I am trying to use model clause to get comma separate single row for multiple rows. My scenario is like this:

SQL> desc test1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
VALUE CHAR(6)

SQL> select * from test1 order by id;

ID VALUE
---------- ------
1 Value1
2 Value2
3 Value3
4 Value4
5 Value4
6
7 value5
8

The query that I have is:
SQL> with t as
2 ( select distinct substr(value,2) value
3 from test1
4 model
5 ignore nav
6 dimension by (id)
7 measures (cast(value as varchar2(100)) value)
8 rules
9 ( value[any] order by id = value[cv()-1] || ',' || value[cv()]
10 )
11 )
12 select max(value) oneline
13 from t;

ONELINE
---------------------------------------------------------------------------------------------------
Value1,Value2,Value3,Value4,Value4,,value5,

what I want is : null value should not come and duplicate value should not come (Value4 in output above)

View 11 Replies


ADVERTISEMENT

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

PL/SQL :: Model Clause Returning Phantom Value

Sep 25, 2012

I have created a amortization SELECT statement that amortizes loans based upon a table of loan information. The problem is this: when I run an ITERATION on numbers, the select statement returns a value but the ITERATION seems to be reading zero. This leads to a bunch of zeroes in the resultant table.

DROP TABLE MORTGAGE_FACTS

CREATE TABLE mortgage_facts (
C_USER1 CHAR(15),
C_USER9 NUMBER,
C_USER11 NUMBER,
C_USER10 NUMBER,
C_B1 NUMBER(19,2),
C_B2 NUMBER(19,2),
C_BUDGET NUMBER(19,2),
C_USER12 NUMBER(19,2));

INSERT INTO mortgage_facts VALUES ('Jones', 131828.81, 3.348, 72, 0, 0, 0, 0)

--BEGIN AMORTIZATION

SELECT
P,
C_USER1,
C_USER9,
C_USER11,
C_USER10,
C_B1,
C_B2,
[code]........

The first two results look like this:

P C_USER1 C_USER9 C_USER11 C_USER10 C_B1 C_B2 C_BUDGET C_USER12
0 Jones 131828.81 3.348 72 0 0 131828.81 2023.55
0 Jones 131828.81 3.348 1 367.8 -367.8 132196.61 2023.55

As you can see, in column C-B2, despite referencing the column C_USER12 which should have 2023.55 as a value in it, it is returning the mirror opposite of C_B1 which leads me to believe that the system is reading a ZERO in the column despite displaying 2023.55.

View 6 Replies View Related

SQL & PL/SQL :: Separate String Based On Comma?

Oct 28, 2013

This I want TO separate TO different COLUMNS based ON comma.

THE RULE IS LIKE out OF total five fields FIRST 3 comma will be FIRST 3 addresses AND rest will be address4 AND LAST NUMBER should appear IN pincode field IF found.

The trouble is for reading reverse to get the number.

WITH address AS (SELECT 'Avenue Supermarts Pvt Ltd,Anjaneya, Opp Hiranandani Foundation School, Powai, Mumbai,Pin Code 400076' addr1 FROM dual UNION ALL
SELECT 'Plot No. J-I, Block B-I, Mohan Co-operative Industrial Area, Mathura Road, New Delhi-110044' addr1 FROM dual UNION ALL
SELECT 'Padmashree Arcade, NH 5, Chinagantiyda Main Road, Gajuwaka, Vishakhapatnam' addr1 FROM dual UNION ALL
SELECT 'The Icon, 2nd 3rd Floor, #8, 80 Feet Road, HAL III Stage, Indiranagar, Banglore-560075' addr1 FROM dual UNION ALL
SELECT '13/1, International Airport Road, Bettahalasur Post, Bengaluru-562157' addr1 FROM dual)
SELECT addr1 FROM address;

View 10 Replies View Related

SQL & PL/SQL :: Getting Multiple Rows To Single?

Mar 24, 2010

I have records:

owner company
A X
A Y
A Z
B X
B Y
C X

owner companyX companyY companyZ
A 1 1 1
B 1 1 0
C 1 0 0

How do I write the SQL?

View 2 Replies View Related

SQL & PL/SQL :: How To Convert Single Row Into Multiple Rows

Feb 28, 2012

CREATE TABLE T1 ( id NUMBER,
START_date DATE,
end_date DATE,
end_date1 DATE,
end_date2 DATE,
end_date3 DATE,
LEVEL1 number
)
/

[Code]...

I have data in the first table as mentioned above I need to insert multiple rows into the second table for the same ID depends on the level, If it is level 1 then two rows for same ID first reocrd start_date as the start_date and end_date as end_date from the table t1 for second record start_date is end_date in t1 and end_date for this record is end_date1 column in table t1.

If the level is 3 then the table t2 should have four records for one id and the phase is the value for each record for one ID for example in level 3 we have 4 records for one id and phase should be 1,2,3,4.

View 3 Replies View Related

SQL & PL/SQL :: Transform Single Row Into Multiple Rows?

Mar 22, 2013

writing the sql, to transform a single row into multiple rows. I am trying to create multiple rows based on a value of a column in the table.In the below example, I am trying to create the rows based on the 'Col2' values. find the below example:

Original table data:

Col1 Col2 Col3 Col4

Row1 a1 a,b,c 01 ON
Row2 b1 d,e,f 02 OFF
Row3 c1 g,h 03 ON

I want the above table to be transformed into below:

Col1 Col2 Col3 Col4

Row1 a1 a 01 ON
Row1 a1 b 01 ON
Row1 a1 C 01 ON
Row2 b1 d 02 OFF
Row2 b1 e 02 OFF
Row2 b1 f 02 OFF
Row3 c1 g 03 ON
Row3 c1 h 03 ON

View 2 Replies View Related

Fetching Multiple Rows For Single Column

Jun 25, 2012

I am trying to write a script where a particular post code from a table is having more than 3 telephone numbers.Both the columns are in the same table. How to fetch.

Table is P_Order
Columns are DELIVERY_POSTCODE and TEL_NO...
Condition DELIVERY_POSTCODE has more than 3 TEL_NO

View 1 Replies View Related

Update Multiple Rows With Different Values In A Single Statement

Jul 24, 2009

Updating multiple ROWS with different values using single statement. Requirement is to update one column in a table with the values in the other table.

Say we have 3 tables, CORPORATION,CORPORATE PROFILE and MEMBER.

Each MEMBER has CORPORATE PROFILE which in turn is associated with CORPORATION. Now I need to update MEMBER table with CORPORATION identifier for members who belong to corporations with identifiers say 'ABC' and 'DEF'.

MEMBER table contains column 'CORPIDENTIFIER '. CORPORATEPROFILE table contains MEMBERID and CORPORATIONID,this will associate a member with the corporation. CORPORATION table contains ID and CORPIDENTIFIER.

Using the below query I am getting error,ORA-01427:single-row subquery returns more than one row

UPDATE MEMBER M SET M.CORPIDENTIFIER=
(SELECT A.IDENTIFIER FROM CORPORATION A,CORPORATEPROFILE B
WHERE B.CORPORATIONID=A.ID AND B.MEMBERID=M.ID AND (A.IDENTIFIER LIKE 'ABC' OR A.IDENTIFIER LIKE 'DEF'))

Sub query in the above query returns multiple rows and hence it is throwing the error.More than one members are associated with Corporations ABC and DEF. Is there any way possible to update all the rows in single query with out iterating the result set of sub query.

View 1 Replies View Related

Inserting Into A Table By Selecting Multiple Rows Into A Single Row

Jul 27, 2012

I have a flat file as source wherein I am getting values like

Comp_id, Comp_name, ISIN, column_name, column_value

The structure is like this may contain multiple records like Comp_id, Comp_name, ISIN will be same, but column_name will contain the column_name to which its corresponding column_value needs to be populated to.

E.g. of Feed File -

Comp_id, Comp_name, column_name, column_value

1,HSBC,branch_name,HSBC-DELHI
1,HSBC,branch_add,24-Lajpat Nagar
1,HSBC,branch_phone,2322322
2,HSBC,branch_name,HSBC-MUMBAI
2,HSBC,branch_add,24Andheri
2,HSBC,branch_phone,4445221
2,HSBC,branch_postalcode,400023

Target table structure

Comp_id, Comp_name, branch_name, branch_add, branch_phone, branch_postalcode

I need to insert the above data to a table by selecting data from above scenario.

View 10 Replies View Related

SQL & PL/SQL :: Updating Multiple ROWS With Different Values Using Single Statement?

Feb 16, 2011

The requirement I have is :

I have two tables eim_asset and eim_asset1.I want to update the table eim_asset1 using the following update SQL (Or Logic)

update eim_asset1
set emp_emp_login = (select login from s_user where row_id in
(select row_id from s_emp_per where row_id in
(select pr_emp_id from s_postn where row_id in
(select position_id from s_accnt_postn where ou_ext_id in
(select row_id from s_org_ext where row_id in
(select owner_accnt_id from s_asset where owner_accnt_id is not null)))))

It gives me the ORA error : ORA-01427:single-row subquery returns more than one row.know why I am getting it, because of the one-to-many relationship between owner accounts and their assets.

View 1 Replies View Related

SQL & PL/SQL :: Updating Multiple Rows With Single Update Statement?

Aug 20, 2013

create table temp_tst
(
FILENAME VARCHAR2(200),
EDITED_BY VARCHAR2(50),
EDITED_TO VARCHAR2(50)
)

[code]....

Can I write a single update statement to update filename column replacing "_tst" with "_check"?

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

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 :: Unable To Pass Comma Separated Values For In Clause

Apr 24, 2013

I have the following query : for :P_LEG_NUM Parameter when i am passing values like 1,2,5 as string type i am getting invalid number error... I have defined in clause for it but still it does not work.. For individual values like 2, etc it works... how can i pass comma separated values for this bind variable

select trip_number as prl_trip_number,
flight_number as prl_f_number,
trip_leg_id as prl_trip_leg_id,
leg_number as prl_leg_num,
dicao as prl_dicao,
[code]........      

View 2 Replies View Related

SQL & PL/SQL :: Concatenate A Comma But Not Single Quotes

Oct 8, 2010

In table_A , the primary key is Col_A which is of data type number.I want to concatenate it with ' '

Col_A
______

123
124

select '|| col_A ||'||','
from
Table_A;

The output should be
'123',
'124'

I can concatenate a comma but not single quotes.

View 3 Replies View Related

PL/SQL :: Rows Into Comma Separated Values

Oct 30, 2012

I would like get rows into comma separated values expected output

rowvalue1,<space>rowvalue2,<space>rowvalue3,<space>rowvalue4,.....Example:

create table test1 (name1 varchar2(10));

insert into test1 values ('JOHN');
insert into test1 values ('YING');
insert into test1 values ('KAREN');
insert into test1 values ('PEDRO');
commit;

SQL> select * from test1;

NAME1
----------
JOHN
YING
KAREN
PEDROHow can I get this to printed as
JOHN, YING, KAREN, PEDRO

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

PL/SQL :: Comma Separated String In Rows

Oct 23, 2013

I have one table   select * from abcd;

No  err-----------------------------1    rishi,rahul2    rishi,ak I want output like: 

No ERR1 rishi1 rahul2 rishi2 ak i am using the below query for this: 

select  no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null  but this query is giving me output: 

1rishi1rahul2ak2rishi1rahul2ak if i am using distinct then only desired output is coming. select distinct  no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null but i don't want to use distinct because my table has millions of rows and err contains comma separated varchar(6000);

View 4 Replies View Related

SQL & PL/SQL :: Multiple Search Terms Will Be Separated By A Comma

Dec 12, 2011

I am building a search for use in one of our major applications. I have written a PL/SQL package that deals with it. I would like to present the requirement list to the group and see what, if anything, you may have done differently than I have.

1.) The search interface must have a single box, like google.

2.) Multiple search terms will be separated by a comma.

3.) The table has the following columns:
-- Name
-- Title
-- addr
-- addr2
-- city
-- state
-- zip
-- phone
-- email

4.) Number of Search Terms per query will be unlimited. (for now, as practicality dictates)

5.) Each search term will be checked against various columns.

6.) Search terms must not have a preference in order. Name, Address = Address, Name

7.) Records will be returned only for the rows where all search terms are found.

View 3 Replies View Related

SQL & PL/SQL :: Convert ID And Comma Delimited List To Rows

May 31, 2011

how can I convert

select 1 as id, 'role1,role2,role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual

to

select 1 as id, 'role1' as roles from dual union all
select 1 as id, 'role2' as roles from dual union all
select 1 as id, 'role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual

?

I would prefer sql then plsql. Script for creating a test table:

create table CONVERT_LIST(id integer, roles varchar2(100));
insert into CONVERT_LIST values(1,'role1,role2,role3');
insert into CONVERT_LIST values(2,'role1');

View 3 Replies View Related

SQL & PL/SQL :: How To Display The Status And Record Count In Separate Rows

Mar 6, 2013

My problem is i need to display the status and the record count in separate rows.How can i do it

Example

SELECT 'A' STAT, COUNT(A) CNT,'B' STAT, COUNT(B) CNT,'C' STAT, COUNT(C) CNT
FROM TAB1;

OUTPUT SHUD BE LIKE

STAT COUNT
A A_cnt
B B_CNT
C C_CNT

View 13 Replies View Related

Application Express :: Separate Link For Each Series On Multiple Series Bar Chart From One Query

May 13, 2013

I have a query that selects multiple series for a bar chart (I have defined only one chart series, and have 3 columns selected to represent 3 series). The series display on Bar Chart is fine. Now I want to create a separate link target depending on which series is clicked on the chart. I am not able to do this.

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

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

PL/SQL :: Dynamic WHERE Clause With Multiple ORs?

Jul 1, 2013

I need to build a SQL statement dynamically such that the WHERE clause looks/works as follows: WHERE x LIKE '%1%' OR x LIKE '%2%' OR x LIKE '%3%' .

View 2 Replies View Related

Multiple Tables With (UNION All) Clause

Aug 9, 2012

I have a view that contain multiple tables with ( UNION all ) clause , is there any way that if i query from this view I can explicitly specify the table that i need the data from ?

Let say i have view that contain salaries of 2011

Create view sals_2011 as select * from sals_jan2011 union all sals_feb2011 ..... union all select * from sals_dec2011.

if i issued select * from sals_2011 where emp_id >500 and date < 01-feb-2011 the explain plan show me that full tables and indexes are in processing, while i know that i need only to scan sal_jan2011, and of course it is taking much longer time than selecting from the original table only.

I am using oracle RAC 11g R2

View 5 Replies View Related

PL/SQL :: Multiple Partition Names In From Clause Of SQL?

Sep 23, 2013

 I want to use multiple partitions in from clause of SQL statement. select c1 from tab1 partition (part1,part2); However getting below error message. 

ORA-00933: SQL command not properly ended00933. 00000 -  "SQL command not properly ended"
*Cause:   
*Action:Error at Line: 3 Column: 43 Above query is sample query.

Real query is big and hence i cant write using UNION ALL to access multiple partitions.

View 12 Replies View Related

SQL & PL/SQL :: Passing Multiple Values To IN Clause In Function?

Jun 22, 2010

I have a function that returns the total sum of an account. From reports I call the function passing the account code. The function sums the values for that specific account code and returns the value. In my function I have the following code :

where account_code = P_CODE.

Eg. The value of :P_CODE is 'CS'.

I now want to pass multiple account codes ('CS','TV',LJ') to the function. How do I change the IN clause in the function to accommodate multiple values.

I have tried using the instr function, but it does not work. eg. AND instr(o.ACCOUNT_CODES,','||P_CODE||',') > 0

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







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