SQL & PL/SQL :: Convert Rows As Column-Decode
Jul 3, 2012
I am having a table with following data:
DateProduct_nameQuantity_sold
12/6/2012Product11
12/7/2012product23
12/6/2012product34
12/7/2012Product12
12/7/2012product21
12/6/2012product35
12/7/2012product33
12/6/2012product24
Now I want this data in following manner:
For each date - how much is the Total quantity sold , sum of Product1 sold,Sum of product2 sold,etc. I am having more than 50 products in my table.
dateQuantity_soldProduct1Product2Product3
12/6/2012141 49
12/7/201292 43
I was trying this by decode but the sum of the product was not correct.
View 2 Replies
ADVERTISEMENT
Sep 25, 2012
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
View 2 Replies
View Related
Mar 22, 2012
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(,)
View 3 Replies
View Related
May 10, 2012
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.
View 4 Replies
View Related
Nov 20, 2011
how to convert rows data into column...
View 7 Replies
View Related
Dec 11, 2009
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.
View 7 Replies
View Related
Dec 11, 2012
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.
View 9 Replies
View Related
Jun 8, 2011
I have job, working hours, employee id, employee name in test_emp table. The job name and employees are not fixed in this table and it varies from project to project. We don't know how many employees are there and needs to be fetching on runtime.
I have the data like below
JOB---------WRKHR---EMPID-----EMPNM
ANALYST-----10------5478------RAMESH
MANAGER-----10------4258------SACHIN
LEAD---------10------4789------VIVEK
DEVELOPER---20------5632------ROHIT
ANALYST-----20------5843------VIRAT
MANAGER-----20------4789------VIVEK
PROGRAMMER-30------5479------SURESH
LEAD---------30------4258------SACHIN
DEVELOPER---30------5231------PRAVEEN
I need the output like below format.
JOB---------RAMESH--SACHIN--VIVEK--ROHIT--VIRAT--SURESH--PRAVEEN--TOTAL
MANAGER-----0--------10-------20-----0-------0------0---------0---------30
LEAD---------0--------30-------10-----0-------0------0---------0---------40
ANALYST-----10-------0---------0-----0------20------0---------0---------30
DEVELOPER---0--------0---------0-----20------0------0---------30--------50
PROGRAMMER-0--------0---------0------0------0------30--------0---------30
--TOTAL-----10-------40--------30-----20-----20-----30--------30--------180
View 6 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
Oct 20, 2011
I had written follwoing sql with CASE statments, but in oracle 8i i'm unable to use CASE in pl/SQL.
how to convert following sql to DECODE ? Or is there any option other than DECODE ?
SELECT ol.order_no,
ol.transaction_line_id,
ol.pd_actl_tonnage,
ol.real_tonnage_high,
ol.req_tonnage,
il.id,
il.transaction_ref,
[code].....
View 18 Replies
View Related
Jul 24, 2007
Is it possible to decode based off a different column? I have a status column that i want to change the value of with a decode, but only if my date column has been populated.
So if status has a value of "New" and my date column is null, then i want it to stay new. If it is populated, i want my status to change to "released"
View 5 Replies
View Related
Jul 26, 2013
i want to decode a column(aan_status) of a datablock based on table. the column have value in the form of 0 and 1, and i want to display this value as yes(0) or no(1) on my oracle form. how can i do this and where should i put my code???
i tried this by
set_block_property('aan_occupant_vu', DEFAULT_WHERE, qry1);
go_block('aan_occupant_vu');
clear_block(no_validate);
SYNCHRONIZE;
execute_query;
[Code]...
but if else part of my code is not working, as i got the data still in 0 and 1 format, not in 'YES' or 'NO' format.
View 7 Replies
View Related
Apr 9, 2010
I have to conditionally update a set of columns in a table. If the column status_code_stage_3 IS NULL THEN I have to update the column status_code_stage_2. The below query is giving error:-
Test Table create scripts
CREATE TABLE test11( date_stage_3 date, reason_code_stage_3 varchar2(20),
reason_code_stage_2 varchar2(20), opportunity_date date )
INSERT INTO test11 values(sysdate,'reason1',NULL,sysdate)
INSERT INTO test11 values(sysdate,NULL,'reason2',sysdate)
[code]....
how to work out the update statement to use the conditional statement for columns.
View 2 Replies
View Related
Sep 17, 2012
I use Oracle 11g and a Middleware which sends the SQL Statements to the database. That's why I can't send you loggings.
However, I' got this SQL:
With CTE
AS
(
select * from
data A2
WHERE
A2.ID ='536031'
AND
A2.AttrID = '6'
)
That code gives me the following result:
ID.........Version....AttrID.......group
536031......3.........6..............test5
536031......3.........6..............test6
What I try to do. I want to get all groups. The Groups should be in one column...the the result must be the following:
ID.........group
536031...test5, test6
In SQL server I always used
SELECT ID,
STUFF((SELECT ',' + [group] FROM CTE WHERE ID = c.ID ORDER BY [Version] FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT ID FROM CTE)c
to achieve that. Is there a similar function in oracle like XML PATH? Or another way the get the result?
View 12 Replies
View Related
Jul 19, 2013
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
View 4 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 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
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
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
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
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
