I have input like below
ID | Name
--------------
1 | ABC, BCA, AAA, BBB (all in one column)
2 | ABC,DBA
and I want to get concatenated values separated
ID | Name
--------------
1 | ABC
1 | BCA
1 | AAA
1 | BBB
2 | ABC
2 | DBA
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
I have not found a solution for this, but it could be that I don't know what to search for.
I need to edit the following statement:
SELECT a.id||a.name||a.amount*2 as transaction
FROM a
WHERE a.amount IN (500, 1000)
To return:
TRANSACTION
------------------------------------
123SMITH1000
123SMITHADJUSTED AMOUNT PER X
456JONES2000
456JONESADJUSTED AMOUNT PER X
The returned rows need to be in this format to be executed in another database.
Can this be done?
I am trying to pass the concatenated value as parameter to the select statement. But it is not returning the results.
If i pass mytrx(1) := '123' it is working fine. It is failing when i pass more than one value.
Table script
create table xxtesttrx (trx_number varchar2(50));
insert into xxtesttrx
values('123');
insert into xxtesttrx
values('456');
Code
declare
-- declare the table type
TYPE xxtest IS TABLE OF
xxtesttrx.trx_number%Type
INDEX BY BINARY_INTEGER;
[code]......
Database version: 11.2.0.3.0
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
Create table a ( Objectid number, Value varchar2(2000);
/
Insert into a values (12, '2,3,4');
Insert into a values (13, '8,7,4');
Insert into a values (14, '3,8,9');
Insert into a values (15, '6,3,11');
I should get the output as:
ID Value
------ ------
12 2
12 3
12 4
13 8
13 7
13 4
14 3
14 8
14 9
15 6
15 3
15 11
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
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);
Here is what i have in ms-sql, how to convert this into t-sql ?
@MortgagePurposeID is parameter with comma seperated values ('1,2,3,4')
if(substring(@MortgagePurposeID, LEN(@MortgagePurposeID)-1,1)<>'','')
Set @MortgagePurposeID = @MortgagePurposeID + '',''
Set @pos=0
[Code].....
with t as
( select 1 id, 101 book_id, 'MICROBIOLOGY' book_type, 1 category, 'sCIENCE AND TECH' category_name
from dual
union all
select 1 , 101 , 'MICROBIOLOGY', 2 , 'HEALTHCARE' from dual
union all
[Code]....
id book_id BOOK_TYPE category category_name
1 101 MICROBIOLOGY 1 SCIENCE AND TECH
1 101 MICROBIOLOGY 2 HEALTHCARE
1 102 CHEMISTRY 5 CHEMICAL ENGINEERING
2 105 COMP SC 1 SCIENCE AND TECH
The above is the output for a query after joining multiple tables. I have just put here the output I am getting after joining the tables.Now I want to achieve the below result.
Expected output: it should be | delimited
1|101|MICROBIOLOGY|102|CHEMISTRY|1|sCIENCE AND TECH|2|HEALTHCARE
2|105|COMP SC|1|SCIENCE AND TECH
Is there any alternative way other than SYS_CONNECT_BY_PATH? I also tried to use CONCAT_ALL but its not working.
I am posting the table and data, i just want to display the columns values separated by ','.
SQL> create table t(c1 varchar2(10),
2 c2 varchar2(10),
3 c3 varchar2(10),
4 c4 varchar2(10));
Table created.
SQL> insert into t values ('A','B','C',null);
1 row created.
SQL> insert into t values ('A','B',null,'D');
1 row created.
SQL> insert into t values ('A',null,'C','D');
1 row created.
[code]....
But it giving extra comma in 1,5,8 rows.
I have a table with three columns X, Y and Z.The data in Column z is of the type 20/1425SE, 13/1235NW.Is there a way to split the data entries where Z LIKE '%/% and insert them as two separate rows.
I don't want to have any entries with '/'. Can these be deleted along with splitting the data entries?
I have a requirement wherein I need to enforce certain column to have only comma as a delimiter, where multiple values exists.
Example:
Create table:
create table test_oz ( slot number, server_name varchar2(50), used_by varchar2(50),
constraint test_oz_pk primary key (slot, server_name) );
Insert Test Data:
insert into test_oz values ( 1,'SRV1','SAMMY' );
insert into test_oz values ( 2,'SRV1','SAMMY,TOM' );
commit;
Data:
SQL> select * from test_oz;
SLOT SERVER_NAM USED_BY
---------- ---------- ----------
1 SRV1 SAMMY
2 SRV1 SAMMY,TOM
From above, the USED_BY column data need to be only comma separated. Is there a way to enforce that?
When user tries to insert data using any other delimiter, it should fail.
I was trying to see if a CHECK CONSTRAINT could be of use, but could not find it to work.
I need to write a function which will take table name as input and should return all the columns separated by coma (,).
For example I have a table product as
PROD_ID PROD_NAME FAMILY_ID
------------------------------------
100006Acetaminophen100005
100013Simvastatin100007
100014Ezetimibe100008
100015Simvastatin+Ezetimibe Oral Family100009
100003Abacavir100003
100007Amlodipine100006
100001Cetirizine HCl Oral Solution100001
My function should return the output as
100006,Acetaminophen,100005
100013,Simvastatin,100007
100014,Ezetimibe,100008
100015,Simvastatin+Ezetimibe Oral Family,100009
100003,Abacavir,100003
100007,Amlodipine,100006
100001,Cetirizine HCl Oral Solution,100001
Is there any inbuilt function available?
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]........
I have a string like this .
'ABC-XYZ-MNO'
and i want the data in the below format
'ABC','XYZ','MNO'
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies View Relatedwhich is the better option to use when the quote needs to be concatenated to a varchar2 value i.e.
In order to insert 'test' into a column exactly as yet see I used:
chr(39)||'test'||chr(39)
However, the DBA (OCP) says that is not a good way to do it and should be changed to:
''''||'test'||''''
CREATE TABLE emp
(
code NUMBER(4),
name VARCHAR2(15),
salary NUMBER(5),
ovt NUMBER(4),
sal_mon DATE,
dept number (3)
)
[code]....
9 rows selected.
Required Output is
CODE NAME SALARY OVT SAL_MON DEPTMerge Codes
---------- --------------- ---------- ---------- --------- ---------- -----------
1068 ALI 18500 2385 31-OCT-11 1021068,1793
1908 ASGHAR 17000 1900 31-OCT-11 1021908,1802
1960 ZAHID 16000 775 31-OCT-11 1021960,1952
1188 RAZA 9500 2910 31-OCT-11 1111188
1275 RAHIL 15300 3480 31-OCT-11 1111275,1176
Merge 02 rows of same department having sum(salary) <=20000. select those rows who have lower salary.
I'd like to get all rows for one particular id. I have data stored in two different tables. I'd like the values to be "denormalized" such that for each unique date I want the values(washer_val, widget_val) for each id from each table to be displayed(or null if no data for that date).
I'm not stating the exact requirements correctly
EVENT_DATEWASHER_VALWIDGET_VAL
9/12/20131, 2 1, 2
9/13/20132
9/14/2013 1
I've created two different example views("tables") of my data with a "with" statement(washers, widgets). I tried to denormalize each set of data (washers_val, widgets_val) and then get all the unique dates (unique_dates). I then join the "views" and display all possible rows with their data. This is the type of output I want BUT is there an easier way to achieve it??
with washers as
(select 1 id, trunc(sysdate) event_date, 1 val
from dual
[Code]....
I want to get the values and put them into html template since i want to configure mime settings. My table's name is rawticket_voip and it has 150.000rows and 20 columns so instead of COL1, COL2 what should i write there? How can i get the spesific row's and column's value and how can integrate loops in this html?
'<html>
<table border="1">
<tr bgcolor="Light Blue">
<td>COL1</td>
<td>COL2</td>
[Code]...
I have a table that reads something like this
City Route
NewYork 2
NewYork 4
NewYork 5
London A
London B
Paris X1
I want to assign Routes (concatenated) to an item on page load... as an example for NewYork it should read like this
P2_ROUTE = 2, 4, 5
I am looking to do the above using query, something like this
select (concatenated route)
from Table_A
where
City = :P2_City
how to concatenate variables.?
how to write below query in pl/sql cursor. The help table has two associated tables, help_txt and help_id, which will have strings of data concatenated into one sales contact record. There are multiple lines of text per comment and multiple lines of resolution text at 40 characters per line. The key to the help_text table (id, date,seqno) is the main key to the help_txt table and help_id t table with a sequence added to each table
The formatted string will contain some text and variables with the comment lines (1-10 or more) concatenated first, followed by the resolution lines (1-10 or more). There will be multiple comment and multiple resolution lines. The Cust_Cmnt_Txt lines and the Resolved_Desc lines should be concatenated and formatted in the following string (% marks the variable string) :
'help taken ' %help.Taken_Dte 'received from the following source: ' %help.id. 'Remark Text: ' %help_text (where help_txt_Seq = 1) %help_text (where help_text_seq = 2-10 or more) 'Resolution: ' %help_id_Res_Txt.Resolved_Desc (where help_ID_Txt_Seq = 1) %help_ID_Res_Txt.Resolved_Desc (where help_id_Txt_Seq = 2-10 or more)
Consider the following (example)table.,
TABLE_A
------------------------------
ID DEPT CRS
------------------------------
1 CS CS_100
2 SCIENCE SCI_150
3 MATH MATH_400
4 HISTORY HIS_110
[Code]...
To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,
--------------------------------------------
NO DEPT CRS
--------------------------------------------
1 MATH MATH_400, MATH_550, MATH_230
--------------------------------------------
instead of.,
--------------------------
NO DEPT CRS
---------------------------
1 MATH MATH_400
2 MATH MATH_550
3 MATH MATH_230
---------------------------
I have two tables which have identical schemas, one table (tbl_store) is used to hold the latest version, and the other table (tbl_store_audit) holds previous versions. When the latest record is updated, it is inserted into the tbl_store_audit table as a revision, and the updated details are used as the latest record.
For example: The latest version is held in tbl_store, however the tbl_store_audit may hold 5 records which are the past records used before changes were made - these are seen as revisions.
I want to be able to compare what has changed between each revision in the tbl_store_audit table. For example: Out of the 10 columns, the change between revision 1 and revision 2 was the size from XL to XXL. The change between revision 3 and revision 4 was the size XS to M and price 4.99 to 10.99, and so on.
Eventually i will create an APEX report that will show the user the revision number and what was changed from and to.
I seen in a previous post i need to note my oracle version: Oracle version 10.2.0.4.0
This is my table design:
REQUEST(R_ID,attr1,attr2)WIPS(R_ID,WIP,attr3,attr4)SHIPPING(WIP,attr5,attr6)
How do I limit my query to show the information from REQUEST but only where the all of the wips associated between REQUEST and WIPS are not in the SHIPPING table. For example, the SHIPPING table has all of the WIPS that have been shipped, I only want to show the REQUEST rows where all of the WIPS have not shipped.
I'm Trying to use Listagg function in oracle 11g for concatenating values from different rows,but i'm getting error as FROM KEYWORD NOT FOUND.
Query is:
select listagg(column_name,'') within group (order by column_name) "column_name"
from table_name;
I am an Oracle beginner and I am having some trouble with the following insert query.
I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.
INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338);
INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);
The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"
difference in the values that are returned?
select count(*) from aaa;
COUNT(*)
----------
1000001
select num_rows from dba_tables where table_name = 'AAA';
NUM_ROWS
----------
994202
How to hold the multiple rows values using array? And I have to pass this values to some other procedure.
Ex: SQL> select ename from emp;
ENAME
----------------------
Vetrivel
Dr.Venkat
Vinoth
Sudhakar
Sivaganesh
Senthil