SQL & PL/SQL :: How To Convert Columns Values Into Row In Oracle
Feb 20, 2012
I have a normal table like step 1 and I want to get a output like step 2. get the output from Columns to row in simple sql.
Step 1)
Table Name: Rat_table
------------------------------------------
Column : year and rating_name
year rating_name
------ -----------
2011 'Sucessful'
2010 'Not Rated'
Step 2)
Table Name: Rat_table
------------------------------------------
rating_name 'Sucessful' 'Not Rated'
year 2011 2010
View 14 Replies
ADVERTISEMENT
Sep 26, 2012
I have Table like
ID |A |B |C
---------------------------------------------------
4 |0000 |ALL |6
7 |0000 |ALL |3
8 |TEST |ALL |3
and my expected should be like this
ID |ColumnName |Value
-------------------------------
4 |A |0000
4 |B |ALL
4 |C |6
7 |A |0000
7 |B |ALL
7 |C |3
8 |A |TEST
8 |B |ALL
8 |C |3
in oracle 11g we have the option call pivot and unpivot but i dont know how achive this in 10g.
View 1 Replies
View Related
Nov 18, 2012
I have a requirement to execute the query only once for all the columns mapped to a particular table. Lets take an example..
table t1 has the following columns mapping.
col1 col2 col3 col4
variable 1 table abc col1_abc ----
variable 2 table abc col2_abc ----
variable 3 table xyz col1_xyz ----
variable 4 table xyz col2_xyz ----
variable 5 table xyz col3_xyz ----
variable 6 table xyz col4_xyz ----
In col4 , we have to update the values of col3 (column) ... it is very easy to update the values by executing the query for each column but my requirement is ... I want to execute the query once for a table ( example select col1_abc,col2_abc from table_abc ... this will return always a single value after adding other conditions in where clause) and why I want to do this? .. because there can be multiple (might be 30 variables values)
from a single table.
I have converted the columns into rows by using listagg function now I have lets a dynamic query ( for the above example).
for i in 1.2
loop
l_query := ' select col1_abc, col2_abc from table_abc';
open c1 for l_query;
end loop;
my problem is : how to hold these values and update the above table?
View 2 Replies
View Related
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
Jul 25, 2013
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies
View Related
Jun 18, 2011
how to convert rows to columns ?
View 1 Replies
View Related
May 30, 2007
Below is the schema of a table:
TableName : PropertyValue
Columns: PropertyID Number
Value varchar
ValueID Number
Phone Number
Requirement: Create a view based on the table"PropertValue'. There could be 4 different PropertyIDs for each phone.
e.g.
PropertyID Value ValueID Phone
1 'xyz' null 1234
2 null 11 1234
3 null 12 1234
4 null 13 1234
1 'pqr' null 5678
2 null 14 5678
3 null 15 5678
4 null 16 5678
Required View:
Phone Attrib1 Attrib2 Attrib3 Attrib4
1234 'xyz' 11 12 13
5678 'pqr' 14 15 16
I tried creating the required view using 'CASE' statement and group by but its returning multiple rows.
select case when PropertyID=1 then VALUE end as Attrib1,
case when PropertyID=2 then ValueID end as Attrib2,
case when PropertyID=3 then ValueID end as Attrib3
case when PropertyID=4 then ValueID end as Attrib4
from (
select Phone, PropertyID, ValueID,Value
from PropertyValue
group by Phone, PropertyID, ValueID,Value
)
View 1 Replies
View Related
Aug 3, 2009
i have a table with this data :
id name
1 a
2 b
3 c
4 d
and i want the o/p like this,
col1 col2 col3 col4 col5
id 1 2 3 4
name a b c d
means i want to convert my columns into rows.
View 1 Replies
View Related
Jun 7, 2010
Need to convert columns into rows like expected
service_key consumer_key the_key fin_score_type granu rno
20100201 1 p_1 100MONTH1
20100201 1 p_0 100MONTH1
20100201 1 d_6 100MONTH1
20100201 1 t_2 200MONTH1
20100201 1 d_5 100MONTH1
But it should display like below,All the the_key types as differnet colmns instead of rows,Since need to insert those values in different tables.
expected o/p consumer_key p_code p_val d_code d_val t_code t_values granula
service_key
20100201 1 p_0 100 d_6 100 t_2 200 MONTH1
20100201 1 p_1 d_5 100 MONTH1
View 5 Replies
View Related
Aug 2, 2010
consider the following tables
WITH table1 AS -- this table contain a list of column names
(
SELECT 11 cid, 'TEK' group_nm, 'TYPE DESC' column_nm FROM dual
UNION ALL
[Code].....
i have 3 tables, one that contains name of columns(table1), another one contain number of rows (table2) and the last table contain
the values for columns in table1 for each row in table2.
what i want to do is join all 3 tables and display the output as follow
TYPE DESC NAME P DATE TYPE_PCT
====================================================
Good John 8/21/2010 0.009
BAD Ken 4/11/2010 10.009
Medium Rob 8/1/2010 0.001
as you can see the columns names comes from table1, and the values comes from table3. i want to join these tables so that it display the output above
View 2 Replies
View Related
Apr 24, 2012
How do I convert rows in oracle table to columns by writing a query?
Here is my table.
EID QNo Qual YOP
101 1 B.Tech 2004
101 2 M.Tech 2006
102 1 B.Tech 2003
102 2 M.Tech 2005
102 3 MBA 2007
Now I want the above table in the following format......
EID Qual1 YOP1 Qual2 Yop2 Qual3 Yop3
101 B. Tech 2004 M.Tech 2006
102 B. Tech 2003 M.Tech 2005 MBA 2007
I have maximum of 8 Qualifications, how to achieve this in oracle.
View 3 Replies
View Related
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
Jun 19, 2013
the query used is
SELECT LOC_CD,
TO_CHAR(DT,'fmMon RRRR'),
SUM(QTY) ,
GROUPING_ID(LOC_CD)
FROM WIP
WHERE LOC_CD IN (1,2,3,4)
GROUP BY ROLLUP(LOC_CD),
TO_CHAR(DT,'fmMon RRRR'),TRUNC(DT,'MM')
ORDER BY TRUNC(DT,'MM'),LOC_CD
This query result attached
The red coln is the total I want to place it in row-wise
Date loc_1 loc_2 loc_3 loc_4 Total
May 2012 4,554 6,644 11,198
June 2012 4,986 5,838 777 11,601
22,799
View 1 Replies
View Related
Feb 19, 2013
Im using oracle 11g.
I have data in a table as below: Column names are type,month,percentage
TYPE MONTH PERCENTAGE
A1 JAN-2013 100
A2 JAN-2013 100
A3 JAN-2013 95
A4 JAN-2013 98
[Code]...
The desired output using a sql query is as below
TYPE JAN-2013 FEB-2013
A1 100 100
A2 100 99
A3 95 88
A4 98 67
A5 99 98
A6 90 95
A7 92 84
View 7 Replies
View Related
Mar 8, 2010
how i can convert rows into columns as tabular form.
I have two table : om_supplier
supp_code
supp_name
om_supplier_term
st_supp_code
terms
like if i want to make query form from supp_code to supp_code the output is comming
[code]
select supp_code,terms from om_supplier,om_supplier_term
where supp_Code = st_supp_code
[code]
supp_code terms
1 90days
1 180days
1 120days
i want the output like
supp_code
1 90days-180days-120days
how i can achieve this in form.
View 8 Replies
View Related
Mar 25, 2010
I have created a object type as
create type emp_obj_dtl as OBJECT (ename varchar2(50),mgr NUMBER)
create type emp_dtl_obj_typ as TABLE of emp_obj_dtl
Using the these object i have created on function as
CREATE OR REPLACE FUNCTION emp_test_func (peno NUMBER)
RETURN emp_dtl_obj_typ
AS
lv_emp_dtl emp_dtl_obj_typ := emp_dtl_obj_typ ();
BEGIN
SELECT emp_dtl_obj_typ(emp_obj_dtl (ename, mgr))
INTO lv_emp_dtl
FROM emp
WHERE empno = peno;
RETURN lv_emp_dtl;
END;
Now if i am executing query as
SELECT empno, emp_test_func (empno) emp_dtls
FROM emp
It is returning me the data as
EMPNO | EMP_DTLS
7500 | (DATASET)
7382 | (DATASET)
7569 | (DATASET)
7800 | (DATASET)
But I want the result set as
EMPNO | ENAME | MGR
7500 | SMITH | 7863
7382 | JAMES | 7896
7569 | KING | 7856
7800 | SANGR | 7456
How to get the resultset as above.
View 4 Replies
View Related
Mar 11, 2013
I have a Employee table of the following format:
Emp_id | Emp_name | Salary
101 | James | 1000
102 | Julia | 200
I have to convert or transpose the table data as follows using a SQL statement/function -
Emp_id | 101 | 102
Emp_name | James | Julia
Salary | 1000 | 2000
How do I achieve this?
View 4 Replies
View Related
Mar 16, 2010
I tried to convert rows to columns using the pivot method.But i am not able to do that.This is what i tried:
SQL> desc pt
Name Null? Type
----------------- -------- ------------
NAME VARCHAR2(10)
YEAR NUMBER(4)
VALUE NUMBER(4)
[code]....
As you can see from the above output i am not getting as expected.
Expected output:
YEAR JOHN JACK MARY
---------- ---------- ---------- ----------
1993 3000 1340 8700
1991 1000 1500 1250
1992 2000 1200 2323
View 7 Replies
View Related
Dec 5, 2012
I have the situation like i want display the data from table which is storing in rows into a columns....
My table structure will be like this(Here i am taking only one part but have N no. of parts in my system)
Partno Purchase date Qty
111 02-DEC-2012 15
111 03-DEC-2012 25
111 04-DEC-2012 20
*** **** ****
111 31-DEC-2012 28
So i am expecting my query out put should be in column wise, that should make columns dynamically based on dates.
like example
__Partno 01-Dec-12 02-Dec-12 03-Dec-12 04-Dec-12 ---- ---- --- 31-Dec-12__
111 0 15 25 20 28
Here columns should be created based purchase dates dynamically with respect to quantity. Query out put will be like matrix format. So i feel that PIVOT & SYS_CONNECT_BY_PATH will not serve my requirement.
My system comparability is
oracle 10g database.
oracle 6i forms.
View 4 Replies
View Related
Oct 5, 2012
CREATE TABLE CFL (q_id Number(18),per_id number(18),PERIOD VARCHAR2(15 CHAR), AMOUNT NUMBER);
INSERT INTO CFL VALUES (11, 1, 'JAN-10', 10);
INSERT INTO CFL VALUES (21, 1, 'FEB-10', 20);
INSERT INTO CFL VALUES (31, 1, 'MAR-10', 10);
and so on (12 records for a year with same quota_id, e.g. here it is 1)
[code].......
Also this column values period is dynamic.. it can be for any year.
The select will return values like
Select per_id, PERIOD, amount from cfl
where quota_id = 1
Basically 12 rows will be the output:
per_id period amount
1 JAN-10 10
1 FEB-10 20
1 MAR-10 10
..............and so on
1 DEC-10 10
The result output should be:
Per_id JAN-10 FEB-10 MAR-10.............DEC-10
1 10 20 10................10
View 14 Replies
View Related
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
Mar 30, 2012
I've a query that sum up data from one table. But I've to convert them into rows.
Summary query result.
COL1 COL2 COL3 COL4
1000 2000 3000 4000
Test Table creation script.
CREATE TABLE TEST_TBL
(
COL1 NUMBER(14,2),
COL2 NUMBER(14,2),
COL3 NUMBER(14,2),
COL4 NUMBER(14,2)
) ;
INSERT INTO TEST_TBL VALUES( 1000, 2000, 3000, 4000 ) ;
Expected result
A1 1000
A2 2000
A3 3000
A4 4000
A1, A2, A3, A4 are hard coded fixed values.
I could have done this but not a good idea in case table TEST_TBL is not a single row table but an inline query on 1,00,00,000 records with summary functions. In my table I've a summary query instead of single row table.
SELECT 'A1', COL1 FROM TEST_TBL
UNION ALL
SELECT 'A2', COL2 FROM TEST_TBL
UNION ALL
SELECT 'A3', COL3 FROM TEST_TBL
UNION ALL
SELECT 'A4', COL4 FROM TEST_TBL
View 12 Replies
View Related
Nov 4, 2011
Enterprise Edition Release 10.2.0.5.0
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------
NLS_CHARACTERSET AL32UTF8
SQL>
There is table (VIN_TEMP) in my company database containing following records. It seems like this table should contain some greek language special chracter values instead of this weird data.
SQL> select * from vin_temp;
ATTR
--------------------------------------------------------------------------------
���9999
���9998
���9997
���9997
[code]....
Client are reporting these records as invalid and requesting us to fix. As i investigated i found out, this table was created and loaded few year back. Client sent us one time files which we loaded into this table. I was able to find the code which was actually used to load this table, but unfortunately i was not able to find the raw files where we load this data from...
It seems like Previous Developer specified character set "UTF8" statement, in his sql loader script, to load this data. It seem those file contain some Greek language special character data which was not support by "UTF8" charater set and result in creating those invalid data. My Job is to fix these invalid records and convert them back to its original values which were present in the raw file. I tried to contact client and see if i can find out the raw files but no luck. I tried to use convert function as mention to convert this data from "UTF8" to our current character set format but no luck.
SQL> SELECT attr, dump(attr), convert(attr,'UTF8', 'AL32UTF8') from vin_temp;
ATTR DUMP(ATTR) CONVERT(ATTR,'UTF8','AL32UTF8'
------------------ ---------------------------------------------------------------- -----------------------------
���9999 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,57 ���9999
���9998 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,56 ���9998
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997
[code]....
Here is the script to create table with those type of invalid records.
create table VIN_TEMP
(
attr VARCHAR2(50 CHAR)
);
insert into VIN_TEMP (attr) values ('���9999');
insert into VIN_TEMP (attr) values ('���9998');
[code]....
View 1 Replies
View Related
Dec 17, 2010
We are working on a migration project and we need to move 75 million rows from source system to target system.
Total number of columns in source system - 90 cols.
Out of the 90 columns 10 cols are system fields and rest 80 are properties for each record.
We are required to migrate all system cols and some required properties. In total we will migrate around 25 columns[10+15] for each record.
Before actaul migration , we need to do a data cleansing activity and hence we move the data to a staging table.
To create the staging table, we considered the below appraoches.
1. Create the staging table with around 30 coloumns so as to fit the data from source system[map the columns based on datatype]
2.Create the staging table with actual columns[90 columns] and import only the required properties. The rest all columns will remain NULL.
Do the data cleansing and move to target system.
My question here is, if we go with approach 2, We will not mix the data, as there will be a one-to-one mapping. But many columns will not have data and remain NULL. Will it affect the performance since we deal with 75 million rows.
View 3 Replies
View Related
Dec 20, 2012
I need to add values of one column values from a table to another table each value as a column. Below i am considering only for 3 values in real time i have more than 50 values
CREATE TABLE TEST_REG ( VAL VARCHAR2(1));
INSERT INTO TEST_REG VALUES ('A');
INSERT INTO TEST_REG VALUES ('B');
INSERT INTO TEST_REG VALUES ('C');
CREATE TABLE TEST_HOLD ( COL1 VARCHAR2(1),COL2 VARCHAR2(1),COL3 VARCHAR2(1)); -- in realtime i have 100 columns
Now i need the output as:
when i
select * from test_hold;
COL1 COL2 COL3
A B C
View 6 Replies
View Related
Apr 25, 2011
How to find the duplicate values in two columns.
Suppose we have two columns A and B and the data looks like this
A B
--- ---
1 One
2 Two
1 One
2 Two
3 Three
I need to write a query in such a way that i should find out the duplicate values which are repeating.
View 7 Replies
View Related
Sep 3, 2010
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]...
View 4 Replies
View Related
Sep 6, 2012
I have a row which contains 6 columns where I want that data to be shown in the form of columns as shown here:
From:
select item1, item2, item3, amt1, amt2, amt3 from item_table where sno=1; <----- returns 1 row as below
ITEM1 ITEM2 ITEM3 AMT1 AMT2 AMT3
---------- ------------ ------------- ----------- ------------ ----------
AAA BBB CCC 10.00 20.00 15.00
Data explanation: item1's (AAA) price is amt1 (10.00), item2's (BBB) price is amt2 (20.00) and item3's (CCC) price is amt3 (15.00). OK.
Now I want that data to convert into columns as shown here:
To:
ITEMS AMT
--------- ---------
AAA 10.00
BBB 20.00
CCC 30.00
I want a SQL to display this data.
I found one query which converts a row into columns, but this does not serve my requirement: [for your reference only]
SQL> select substr( the_string
, decode( level, 1, 1, instr(the_string,',',1,level-1)+1)
, decode( instr(the_string,',',1,level), 0, length(the_string), instr(the_string,',',1,level) - decode( level, 1, 0, instr(the_string,',',1,level-1))-1)
) the_value
from ( select (select item1||','||item2||','|| item3 from item_table where sno=1) ITEMS
from DUAL)
connect by level <= length(the_string)-length(replace(the_string,','))+1
View 11 Replies
View Related
Jan 28, 2011
I've recieved a recent request wherein the requirement is to swap values between columns across multiple tables in a database.Following is a visual sample of what needs to be done.
Before Swaping:
Columns Values
TABLE1.IDENTIFIER MRN123
TABLE1.MEDICARENO CHI1234567
TABLE2.MRN MRN123
TABLE3.MRN MRN123
OTHERTABLE.MRN MRN123
After Swapping:
Columns Values
TABLE1.IDENTIFIER CHI1234567
TABLE1.MEDICARENO MRN123
TABLE2.MRN CHI1234567
TABLE3.MRN CHI1234567
OTHERTABLE.MRN CHI1234567
View 12 Replies
View Related
Aug 5, 2013
I am trying a pl/sql block which which take a string and execute it dynamically. Suppose below is string
M_COL := Q'[(P_CODE=> ':DEPTNO',P_CODE_TYPE => 'STATE')]';
Now trying to execute it. using below
M_STR := 'SELECT CHK_DEPT' || M_COL || ' FROM EMP WHERE EMPNO=''7499''';
EXECUTE IMMEDIATE M_STR
INTO M_DATE;
Now what i want is M_STR vairable to executed as
SELECT CHK_DEPT(P_CODE=> DEPTNO,P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
instead of
SELECT CHK_DEPT(P_CODE=> 'DEPTNO',P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
other in other way in parameter P_CODE Column value of DEPTNO should be passed.Also note that DEPTNO column in string M_COL is Dynamic.i.e
M_COL := Q'[(P_CODE=> ':DEPTID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':EMP_ID',P_CODE_TYPE => 'STATE')]';
View 9 Replies
View Related
