I have this following data in a single table
Student
Semester
Subject
Marks
and I want to achieve the following.
I am asked to write a query with the students as Rows with thier subjects and marks as per thier semester which is the columns.
new to this type of queries...This is somewhat like pivot..
Those who have not appeared for a semester should be null just exactly as shown above.Is it posible ?
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
Changing rows to columns without using pivot and aggregate functions?
View 10 Replies View RelatedI need a helo to pivot table with variable columns, I have a pivot table :
SELECT a.*FROM (SELECT codigo_aluno,nome_aluno , id_curso,dia FROM c_frequencia where dia like '201308%') PIVOT (sum(null) FOR dia IN ('20130805' ,'20130812','20130819','20130826'))
a but I need to run the select with values for dia , getting from a other table :
SELECT a.*FROM (SELECT codigo_aluno,nome_aluno , id_curso,dia FROM c_frequencia where dia like '201308%') PIVOT (sum(null) FOR dia IN (select dia from v_dia_mes ))
is it possible to create two head columns sql-pivot.
something like that...
SELECT * from ( Select position,cat_id, att_name as ATTRIBUTE, pro_name, value,competitor
FROM cat_attributes)
PIVOT (
max (value)
FOR (pro_name) IN ('MS 3','MS 4'))
for (competitor) in ('1','1');
what are the collections available in Oracle Plsql, what are concepts of collection.
How to Transpose a Table from rows to columns or columns into rows.
DDL and DML concepts.
What is the concepts of statistics in Oracle Plsql.
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
See attachment for table creation and table data.
The table tbl_completed has two columns, id and completed. I have created a query that does provide me with the result set that I need, but I need to pivot the rows into columns. So instead of a result set to look like this:
select completed, count(completed) as theCount from tbl_completed group by completed;
COMPLETED THECOUNT
Y 772
N 720
I need a result set to look like this:
COMPLETED,Y,N
THECOUNT,772,720
The best solution that I have discovered is this, but I figuring their might be a better way.
select
(select 'THECOUNT' from dual) as COMPLETED,
(select count(completed) from tbl_completed where completed = 'Y') as Y,
(select count(completed) from tbl_completed where completed = 'N') as N
from dual;
COMPLETED,Y,N
THECOUNT,772,720
I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query
MONTH_YEAR RMS RMS_OCC TTL_RMS
---------------------------------------
SEPTEMBER 200917790017790
OCTOBER 2009183831278818347
NOVEMBER 2009177901460517762
and I need to display this as
COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009
--------------------------------------------------------------
RMS 17790 18383 17790
RMS_OCC 0 12788 14605
TTL_RMS 17790 18347 17762
Sample data
col1 col2 col3
1 A someval1
2 A someval2
3 A someval3
2 B someval4
3 B someval5
In col1 there will be always 1 or 2 or 3 value not more than 3 I am using oracle 10g.Want the following output in a single query with using user defined function or stored proc
OUTPUT
newcol1 newcol2 newcol3
someval1 someval2 someval3
null someval4 someval5
Getting error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR Source row:
NOTE_IDCONTRACT_GRANT_IDPROSPECT_IDPROGRAM_CODE
1 1 1 786
2 2 2 786
Program:
SELECT
CASE
WHEN "PIVOT_ROW_GENERATOR"."ID" = 0 THEN
"PIVOT_SOURCE"."ID_NUMBER"
WHEN "PIVOT_ROW_GENERATOR"."ID" = 1 THEN
"PIVOT_SOURCE"."ID_NUMBER"
[code].........
Note: This is not a homework assignment, but rather, a technical bottleneck at work.
My dilemma is such: let's say that I have 1 teacher in an entity, and this teacher has 3 students in an associative entity. So if you did a select T.teacher, S.student from TEACHER T, STUDENT S, where T.teacher_id = S.teacher_id, you would get 3 rows:
Teacher 1, Student 1
Teacher 1, Student 2
Teacher 1, Student 3
How I would like to display it, is in 1 row:
Teacher 1, student 1/student 2/student 3
Is there a way to write a SQL to achieve the above?
how to convert rows to columns ?
View 1 Replies View RelatedBelow 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
)
Table T having columns Name, TCode, TCount...Sample Data:
Name, TCode, TCount
Joe,X,5
Bloggs,X,7
Joe,Y,9
Smith,Y,2
Bloggs,Z,3
This data to be represented as:
Name,X,Y,Z
Joe,5,9,0
Bloggs,7,0,3
Smith,0,2,0
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.
I have a table that has rows like below in one column. I want to parse the rows in different columns.
Column12
abc|yed| qas ert | hub cap | jak
agh|
bef|rfd
som|gfr|lup
I need to parse the above in different columns by splitting at the pipe.
so it will show something like this
col1 col2 col3 col4 col5
abc yed qas ert hub cap jak
agh
bef rfd
som gfr lup
is it possible to do it in PL/sql.
How can i retrieve result of multiple rows into single row. Ex
select ename from emp;
Ename:
--------
ALLEN
WARD
JONES
MARTIN
BLAKE
[Code]...
Result should be like
ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
I have a table with the following structure and data.
create table testing_1 (column_1 varchar2(10),
heading_1 number ,
value_1 number);
insert into testing_1 (COLUMN_1, HEADING_1, VALUE_1)
values ('First', 10, 99);
[code].......
COLUMN_1 HEADING_1 VALUE_1
First 10 99
Second 50 50
Third 10 80
First 50 77
Third 50 70
First 100 55
Third 100 60
And i need the output like below. (i.e. distinct heading_1 (10, 50, 100) will become our new column headings and corresponding values for these headings will be filled in all the rows)
10 50 100
First 99
Second 50
Third 80
First 77
Third 70
First 55
Third 60
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
I have one issue with the converting columns into rows.
Employee table has manager , additional manager and some other columns. If the employee has additional manager, then i need those employee record with 2 rows : 1st row - manager with other data , 2nd row - additonal manager with other data .
Orginal data is like this:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ADDTIONAL_MGR ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 7698
[code]....
I have tried this , using UNION ALL method, But it is taking more time.
SQL> ED
Wrote file afiedt.buf
1 SELECT DECODE (DUMMYTAB.NUM,'1',MGR, '2',addtional_mgr) MANAGER, empno, ename, job, hiredate, sal, comm, deptno
2 FROM emp E, (SELECT '1' NUM FROM DUAL UNION ALL SELECT '2' NUM FROM DUAL) DUMMYTAB
3* WHERE addtional_mgr IS NOT NULL
SQL> /
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 the following query
select a.EV_ACTOR_NAME, round((b.total_transfer/(24 * a.Max_Throughput)) * 100) AVG_ThroughPut,round(((b.total_transfer/a.Max_Throughput) * 100 )) MAX_Throughput from
(select distinct EV_ACTOR_NAME,((max(EV_TRANSFER_RATE) over ( partition by EV_ACTOR_NAME)) * 60 * 60) /1024 Max_Throughput from dprt_events where EV_ACTOR_NAME is not null ) a,
(select distinct EV_ACTOR_NAME, (sum(EV_TRANSFER_SIZE) over( partition by EV_ACTOR_NAME)/1024/1024/1024) total_transfer from dprt_events where EV_ED_ID in (40,105,112) and EV_END_TIME between (sysdate - 20) -12/24 and sysdate - 20) b
where a.EV_ACTOR_NAME = b.EV_ACTOR_NAME
This is the output i get :
EV_ACTOR_NAME AVG_THROUGHPUT MAX_THROUGHPUT
------------ ------------- --------------
actor2 1 33
actor3 0 0
actor1 2 53
How do I convert the above out put to following format.
EV_ACTOR_NAME value1 value2
-------------- -------------- --------------
actor2 AVG 1
actor2 MAX 33
actor3 AVG 0
actor3 MAX 0
actor1 AVG 2
actor1 MAX 55
I tried the following
WITH data AS
(select level a from dual connect by level <= 3)
select EE_ACTOR_NAME,decode(a, 1, AVG_ThroughPut, 2, MAX_Throughput) value from
(
[Code]...
but i always get the following error ORA-00904: "A": invalid identifier
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
I have following table -
CREATE TABLE group_device
( group_id NUMBER (8) NOT NULL
, member_id NUMBER (4) NOT NULL
, device_id NUMBER (10) NOT NULL
, install_date DATE NOT NULL
, remove_date DATE
);
[Code]...
Current output does looks like below
10 1 123 23-MAY-12 28-MAY-12
10 1 456 28-MAY-12 01-JUN-12
10 1 789 01-JUN-12
10 2 999 04-MAY-12 17-MAY-12
10 4 1123 22-JAN-12 27-JAN-12
10 4 1456 27-JAN-12 28-JAN-12
Device_id - Replaced_device_id - remove_date :: For group_id=10
e.g. for member_id = 1, device_id=123 got replaced by device_id=456 on 28-May-12
device_id=456 got replaced by device_id=789 on 01-Jun-12
So output should look like this
10 123 456 28-May-12 -- Replaced
10 456 789 01-Jun-12 -- Replaced
10 789 -- Active
Similarly there will be many groups, so it should list based on group_id
i want to display all the columns of emp table in rows.O/P should be like below:---------------------------------------------Columns------------empnoenamesaldeptnomgr
View 7 Replies View RelatedHow 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.
I have seen lots of examples of using PIVOT to return an sql query where the rows have been converted to columns. All the examples I can find require you to have two columns with multiple rows, and also require you to know the data which is in one of columns to make titles for the new columns.
I need something a little different...I have a query using UNION which goes something along the lines of...
SELECT [get data from one place]
UNION
SELECT [get data from another place]
UNION
SELECT [get data from another place]
this returns the following...
a_val
-----
100
200
300
query to return something like this...
a_val1 a_val2 a_val3
------ ------ ------
100 200 300
The names of the columns don't really matter. And I dont want to create a new table in the database just for the result.
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
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
deptno count(*)
-----------------
10 2
20 2
30 1
I want to display like this(rows into columns)
--------------------------------------------------------
Required Output
-------------
10 20 30
2 2 1
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