I want to convert a column value to a delimited string using a query.
Example
TableA
Col1 Col2 Col3
1 x200 MIS-X
2 x200 BTS-X
3 x200 TYR-X
4 x100 YRY-X
Select Col3 From TableA where Col2 = 'x200'
Expected Output:
'MIS-X','BTS-X','TYR-X'
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');
I have a requirement, where i need to find and replace values in delimited string. For example, the string is
"GL~1001~157747~FEB-13~ CREDIT~ A~N~ USD~ NULL~".
The 4th column gives month and year. I need to replace it with previous month name. For example:
"GL~1001~ 157747~ JAN-13~ CREDIT~ A~N~USD~NULL~".
I need to do same for last 12 months. I thought of first devide the values and store it in variable and then after replacing it with required value, join it back. I just wanted to know if there is any better way to do it?
I am expecting the input to my procedure will be in the following format
'AAA, aaa, Aa12|BBB, bbb, bb2B|dd3, DDDE,ddd67'
I need to convert it to nested table and when I query the nested table , the output should be
column_value
------------
AAA
aaa
Aa1
BBB
bbb
bb2B
dd3
DDDE
ddd67
I need a single select query which converts all the rows into a single value . Below is my requirement :
Create table email_tbl(emailid varchar2(30));
insert into email_tbl('1@y.com');
insert into email_tbl('2@y.com');
insert into email_tbl('3@y.com');
insert into email_tbl('4@y.com');
Now , I need a single select query which gives me the below results.
1@y.com,2@y.com,3@y.com,4@y.com
I have done the above by using a cursors in the pl/sql objects.But want to achieve this with a single sql/query.
I have a below requirement let me know the optimized query.
Create table test_123 ( sr varchar2(1000));
Insert into test_123 values ('1,2,3,4,5');
Insert into test_123 values ('6,7,9,10,11');
Insert into test_123 values ('9,2,3,8,5');
I need output as :
Sr_output
1
2
3
4
5
6
7
8
9
10
11
I need all the values in the column sr in a single column and mutiple rows.
I need to update a column with tab delimited ie that column value should be tab delimited. Eg:- Url_name is the column and column values =[URL]. THe space between [URL] should be tab delimited. I wanted to use a update query to update this values with tab delimited.
View 2 Replies View RelatedExample:
select * from emp
where empno in (123 234 345 124)
I have a date saved in varchar2 colomn.
how can I convert the vaules into date, so I can use the date to compare data.
Ex: I have the value '20-03-2007 05:31:29', but this value is saved as varchar2.
how can I take from this value the date '20-03-2007' as date format?
I want to make a query in which i can convert
alphabet
A
B
C
D
asume i have column name alphabet have data A B C D.But i want to retrieve data like this
Apple
Boy
Cow
Duck
I can't think of a clue how to get the desired results.
I have table column with varchar2 type. I need to convert the strings in this column to ascii values. Now I know Oracle function ascii() converts a character to ascii values. But it just converts the first character of the string and ignore the others. I need to convert the whole string to ascii values. For example, if I need to convert the string "USA", the result should be '858365' (as in U decimal value is 85, S decimal value is 83 and A decimal is 65).
I think I need to use instr() function and also PL/SQL to pass all these strings through a cursor and then use a loop but I'm still confused about it.
I have a string in this format '2011/06/01 00:00:00'. How do I convert this to date format.I tried to_date,to_char and they give errors invalid number & literal does not math format string. I don't have much control on the string since that is the way is comes from the application.
View 3 Replies View RelatedI have a column which has has all numeric values 3 digits to 7 digits in length but all in string format, I want to use seomthing other than to_number function.
Would like to use within the ETL tool, where to_number function isn't getting recognized.
select EMP_NO from employees;
i have a column 'name' in which value is 'Shailesh Negi',i have to insert 'shailesh' into'first name' column and 'Negi' into 'last name' column respectively.
View 20 Replies View RelatedI have a varchar2 column that contains something like this:
10100010
Each digit represents a flag that corresponds to a certain label:
1 - Bananas
0 - Oranges
1 - Apples
0 - Tomatos
0 - ..
0 - ..
1 - Kiwi
0 - Others
What I want to do is, translate '10100010' into the string '(Bananas,Apples,Kiwi)' using SQL-only. As a bonus it would be nice if the string is completely empty if all flags are '0', so no '()'.
basically I have to insert in a date field a string that is a year(four characters).
The problem is that I have to convert a string in date obtaining just a year. How can i do this?
We have a forms application in Forms 10g. We scan a document and gets it to a image item in the Form. Now, we need to pass this image to a 3rd party application (document mgt. system, ). They have given us a web-service for this. We have to convert the image to a BASE64 string and then invoke the web-service method and input it.
The, 3rd party application document management system will also send images as BASE64 strings through the web-service. We need to convert them back to a blob and put to an image item in the form so that it can be viewed by the end-user.
So, how can we convert a image item in form (as BLOB) to a base64 string and also do the reverse???
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
show an ex to use string buffer for select statemnt
View 1 Replies View Relatedhow can i change row as column in oracle.
I wrote the one query which is return the following output..
Query:
SELECT RMC_N_ID,NVL(DISCOUNT_N_ID,NULL) AS DISCOUNT_N_ID,NVL(NULL,'') AS LOADINGS_N_ID,NVL(NULL,'') AS WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_DISCOUNT WHERE RMC_N_ID =224 AND DISCOUNT_N_ID IS NOT NULL
UNION
SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(LOADING_N_ID,NULL) AS LOADING_N_ID ,NVL(NULL,'') AS WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_LOADING WHERE RMC_N_ID =224
UNION
SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(NULL,NULL) AS LOADING_N_ID ,WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_WARCOMB WHERE RMC_N_ID =224
UNION
SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(NULL,NULL) AS LOADING_N_ID ,NVL(NULL,'') AS WARCOMB_N_ID, ENDORSECOMB_N_ID FROM UWRMC_ENDORSECOMB WHERE RMC_N_ID =224
Result:
RMC_N_ID DISCOUNT_N_ID LOADINGS_N_ID WARCOMB_N_ID ENDORSECOMB_N_ID
---------- ------------- ------------- ------------ ----------------
224 0 87 0 0
224 0 88 0 0
224 0 0 0 93
224 0 0 0 94
224 0 0 88 0
224 0 0 89 0
224 82 0 0 0
224 83 0 0 0
I want the result like below
RMC_N_ID DISCOUNT_N_ID LOADINGS_N_ID WARCOMB_N_ID ENDORSECOMB_N_ID
---------- ------------- ------------- ------------ ----------------
224 82 87 88 93
224 83 88 89 94
How can i get this..
my data looks like:-
Ename Job salary
----- ----- -------
A manager 10000
B clerk 60000
C assistant 30000
A manager 50000
B clerk 20000
D accountant 40000
I want the result while select all data should look like:-
A B C D
manager clerk assistant accountant
10000 60000 30000 40000
50000 20000
how to convert row into column
below are my row data from qury
empno ename sal first_name last_name dof
10 rahul 1000 rah rao 01-jan-2000
20 rohit 2000 roh subuu 02-jan-2000
require output
10 20
rahul rohit
1000 2000
rahrao roh
rao subuu
01-jan-2000 02-jan-2000
I have table having below records.
empno ename deptno
101 a 10
102 b 20
103 c 10
104 d 20
105 e 30
Normal Output using Group by.
deptno count(*)
----------------------
10 2
20 2
30 1
I want to display like below(rows into columns)
--------------------------------------------------------
Requiredl Output
----------------------
10 20 30
2 2 1
Deptnos are in first row and cout of deptno nos are second row.
How to convert station & date wise multiple rows to date wise column. I have a table TEST like this,
STN_Name Date State
DELHI1-Sep-201G
DELHI2-Sep-201B
DELHI3-Sep-201G
DELHI4-Sep-201G
DELHI5-Sep-201G
DELHI6-Sep-201M
DELHI7-Sep-201G
DELHI8-Sep-201G
DELHI9-Sep-201G
DELHI ......... ..
DELHI 30 Sep 2012 G
KOLKATA1-Sep-201G
KOLKATA2-Sep-201B
KOLKATA3-Sep-201B
KOLKATA4-Sep-201B
KOLKATA5-Sep-201G
KOLKATA6-Sep-201M
KOLKATA7-Sep-201G
KOLKATA8-Sep-201G
KOLKATA9-Sep-201B
I want output like this
STN_Name1-Sep-20122-Sep-20123-Sep-20124-Sep-20125-Sep-2012............Sep 2012
DELHI G B G G G M
KOLKATA G B B B G M
I have a Table as below
Attribute_namePrimary Seconday
DNS 204.109.167.1204.109.167.2
DNS 204.109.167.2204.109.167.3
NAT 138.20.37.136138.20.37.137
NAT 138.20.20.116138.20.20.117
For a Specific requirement
I need the select data output like below
[b]DNS NAT Type[/b]
204.109.167.1138.20.37.136Primary
204.109.167.2138.20.20.116Primary
204.109.167.2138.20.37.137Secondary
204.109.167.3138.20.20.117Secondary
can we show the following rows into column upto 1000 using sql query?
A
1
2
3
4
[code]...
Result should like this 1,2,3,4,5,6,7,8,...... 1000 including comma(,)
SELECT a.objname, c.property1, c.value1
FROM datatable a, datatabledet c
WHERE a.OBID=c.DATAOBID
and a.CLASSNAME='Class1';
OBJNAME PROPERTY1 VALUE1
280-419-1994psCls1Attr3Attr1Value3
280-419-1994psCls1Attr4Attr1Value4
280-419-1994psCls1Attr5Attr1Value5
[code]....
After query output we put through front end code to make it in the following way i.e. convert rows into columns but with respective data value:
psCls1Attr3 psCls1Attr4 psCls1Attr5 psCls1Attr1 psCls1Attr2 psCls1Attr7
280-419-1994 Attr1Value3 Attr1Value4 Attr1Value5 Attr1Value1 Attr1Value2
280-419-1995 Attr1Value11Attr1Value21Attr1Value71
how to output in the above format using SQL.
I want to convert row data into column and I mentioned here,
This is my table format, A B C------------------col1 1 101col2 2 102col3 3 103
The above query need to like this, COL1 COL2 COL3----------------------------------1 2 3101 102 103
I want to convert join to subquery. how cna i do this.
FROM
TABLE_1 A
INNER JOIN TABLE_2 B
ON ( A.COL_1 = B.COL_1)
[Code]....
how to convert rows data into column...
View 7 Replies View Related