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
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"
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 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?
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.
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
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.
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.
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.
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"?
CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
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
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)
I am facing some problem, while fetching the result that I want to. I have a table with name "test", there are two columns:
"id" type int
"text_data" type varchar2(2000)
Sample Data:
ID TEXT_DATA
------- ------------
10 Hi Deepak, My designation id is dsha21. Thanks Rohit
Now I tried to replace the value for "Deepak","dsha21" and "Rohit" using nested replace function and I succeded but that was for static. Now while creating SQL procedure where I am going to make the values of "Deepak","dsha21" and "Rohit" some static variables. I want to pass the values to be replaced with static parameter.
If I give you simple example of my requirement that would be example of a sms send to all customers by a telephone company. Content is same only the Name of customer is replaced everytime.
need to create a table with single column by using select statement with multiple columns
For Ex- i have 1 row with 10 columns (may be more than 10) like
'A','B','C','D','E','F','G','H',I','J'
i written sql like
select 'A','B','C','D','E','F','G','H','I','J' from dual
result is - 'A','B','C','D','E','F','G','H','I','J' with 10 columns
Now i need output lik this using SQL
Text
------
'A'
'B'
'C'
'D'
[code]...
sort out this problem.
I have a table where i want the output to be a single column
ex: select from t1;*
query output_
rownum col_1
1 8217
2 6037
3 5368
4 5543
5 5232
i would like the result to be : *8217,6037,5368,5543,5232*
i did look on the web but can't find a solution that is easily understood.
I have following tables with data as under:
table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9
I need to generate output as :
column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null
Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?
I has a table of structure of varchar2 datatype.
NO
----------
1-2
3-4
5-6
desired output is:
SQL>1
2
3
4
5
6
The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.
I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.
I have a query which returns nearly 20k rows, as per the requiremnet we need to append all these rows in specific format and insert into single clob column.in the below procedure test_clob.textt is clob field.
CREATE OR REPLACE PROCEDURE pro_test
v_mas_seq NUMBER (9);
v_gov_total NUMBER (20, 2);
v_emp_total NUMBER (20, 2);
v_text_exp CLOB;
v_pageaccess VARCHAR2 (15);
v_dto NUMBER (7) := 4011486;
v_batchno NUMBER (20) :=
[code]....
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.
I want to load single column data into multiple columns.
View 3 Replies View RelatedI have one table , with one column having 2,3 or 4 machine codes , i need to display them as each row per machine code will it be possible to do as i have thousands of records similar to the test case and which i had to do it manually in excel and then upload it back.
create table ow_oper_setup (wo_no varchar2(12),mrk_no varchar2(20),pos_no varchar2(30),mc_code varchar2(60))
insert into ow_oper_setup VALUES ('1270','1270001','W165','IR HO BV ')
insert into ow_oper_setup VALUES ('1270','1270001','W1332','IR BV ')
insert into ow_oper_setup values ('1270','1270001','W1367','RE HO SC BV ')
insert into ow_oper_setup values ('1270','1270001','W389','RE HO SC BV')
commit;
SELECT * FROM ow_oper_Setup;
WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR HO BV
12701270001W1332IR BV
12701270001W1367RE HO SC BV
12701270001W389RE HO SC BV
--i want the output in the following way or the same table data to be replaced as below
WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR
12701270001W165HO
12701270001W165BV
12701270001W1332IR
12701270001W1332BV
12701270001W1367RE
12701270001W1367HO
12701270001W1367SC
12701270001W1367BV
12701270001W389RE
12701270001W389HO
12701270001W389SC
12701270001W389BV
I have a table which has a column that stored concatenated data.
Sample test case is as below:
SQL> create table tst (
2 col1 varchar2(20));
SQL> insert into tst values ('one,two,three');
1 row created.
SQL> commit;
Commit complete.
Is there any way i could write a sql to split the text of this column into rows? Sample output im expecting is as below
col1
-------
one
two
three
I am currently doing column values concatenation from multiple rows and then removing duplicates as in the following example:
SQL> select pid
2 , regexp_replace(ltrim(sentence), '([A-Za-z0-9]+,)1+', '1')
3 from ( select pid
4 , seq
5 , sentence
6 from b
7 model
8 partition by (pid)
9 dimension by (seq)
[code]....
but for some reason regexp_replace does not seem to work with clob and I get:
ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
Is it possible to eliminate duplicates in the model before the concatenation?
I have a schema in which i have 10000 tables and i want to fetch table names from the schema where org_id is not equal to 1,
Note: Here org_id is the column name .
Provide me sql code or procedure to achieve this.
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 want to all rows value into single rows.
for example;
Input data:
Sec_SSC_ID Column_nameAs of dateOld valu New Value
IBM Mat_dt 10/10/20101/1/2001 1/1/2002
IBM Bid Market 10/10/201075 85
IBM asset_nm 1/1/2011International IBM
MSFT asset_nm 1/2/2011Microsoft Intel
MSFT Bid Market price 1/1/201189 90
Output data
I have attached sample output file with it.
how to get this via sql?
I have a table as col_1 number,col_2 varchar2(10)
col_1 col_2
1 abcdefghijkl
what i want is i've to split
col_1 col_2
1 abcdefghij
1 kl
I want to insert multiple records in a single row. Example: I have a below query
select '"'||c1||'","'||c2||'","'||c3||'"'from (select 'ABC' as C1,'ZYX' as C2,'TEST' AS c3from dual unionselect 'A1' as C1,'a2' as C2,'A3' AS c3from dual)
And I want to insert the above 2 records in table T1 as a single row as below:
"A1","a2","A3""ABC","ZYX","TEST" Column1"A1","a2","A3""ABC","ZYX","TEST"
How do I convert multiple rows into single row data. For example.....
I am looking for oracle query for below table output that would have a sql query output as query output below.
I was trying with connect by but was unable to prepare query.
Table output_
---------------------------
ID TYPE VALUE
---------------------------
1012 1 2
1012 6 77
1015 1 1
1015 6 78
1018 1 2
1018 6 79
Query output_
----------------------------
ID VALUE1 VALUE2
----------------------------
1012 2 77
1015 1 78
1018 2 79
