SQL & PL/SQL :: Rows To Columns With Headings

Feb 11, 2011

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

View 6 Replies


ADVERTISEMENT

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

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.

View 4 Replies View Related

SQL & PL/SQL :: Transpose Rows-to-columns And Columns-to-rows

Oct 6, 2010

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

View 3 Replies View Related

Application Express :: Formatting A Table Without Headings

Apr 3, 2013

I want to make a report which is formated (rebuilding an older tool in Access).

So on the one hand I need some formattings which I implemented with the search bar in the interavtive report. (content of the first column is the heading for a table with the rest of the content of all rows which have the sam content in the first column) On the other hand I don't want to show the headings of the columns, what I solved with the classic report(report attributes/ column attributes: none).
Is the a solution to match these two requirements in one report?

View 4 Replies View Related

Reports & Discoverer :: How To Present Report In XLS Format With Proper Column Headings

Dec 12, 2011

I have a report and require the output in xls/xlsx (Excel) format. Currently following properties under System Parameters in Report Builder 6i have been made

Desname = The path for e.g: \C:sz-serverc$ihelp_workingxls
pt_sample_report.xls
Desformat = Delimited
Destype = File

The rest parameters has default settings I have not changed any of the rest User parameter.The report comes up in excel format but totally in distorted manner. i.e; All the column names of report are displayed first in vertical format followed by actual rows for the report.

The requirement is like this

[Select all] [Show/ hide]
Student ID Student Name Fathers Name Guardian Name
1 sample1 sample2 sample3
2 sample4 sample5 sample6

And it is displaying like this

Student ID Student Name Fathers Name Guardian Name 1 sample1 sample2 sample3
Student ID Student Name Fathers Name Guardian Name 2 sample4 sample5 sample6

View 2 Replies View Related

SQL & PL/SQL :: Rows To Columns?

Apr 5, 2012

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

View 3 Replies View Related

Converting Rows To Columns Using OWB

Aug 22, 2013

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].........

View 3 Replies View Related

SQL That Treats Rows As Columns

Oct 29, 2008

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?

View 3 Replies View Related

Rows Into Columns Like Pivot

Sep 5, 2011

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 ?

View 5 Replies View Related

How To Convert Rows To Columns

Jun 18, 2011

how to convert rows to columns ?

View 1 Replies View Related

Convert Rows Into Columns

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

SQL & PL/SQL :: Transform Rows Into Columns

Aug 15, 2011

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

View 4 Replies View Related

Convert Columns Into Rows?

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

SQL & PL/SQL :: How To Parse Rows In Different Columns

Apr 29, 2011

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.

View 3 Replies View Related

SQL & PL/SQL :: Result From Rows To Columns

Oct 29, 2013

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

View 8 Replies View Related

SQL & PL/SQL :: Convert Rows As Columns

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

SQL & PL/SQL :: Query To Get Columns Into Rows?

Aug 10, 2010

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> /

View 15 Replies View Related

SQL & PL/SQL :: Getting The Rows And Columns Values

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

SQL & PL/SQL :: Columns Into Rows Conversion

Mar 24, 2011

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

View 5 Replies View Related

SQL & PL/SQL :: Convert Rows To Columns?

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

PL/SQL :: Query - Rows In Columns

Jul 27, 2012

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

View 8 Replies View Related

PL/SQL :: Display All Columns In Different Rows?

Oct 9, 2013

i want to display all the columns of emp table in rows.O/P should be like below:---------------------------------------------Columns------------empnoenamesaldeptnomgr

View 7 Replies View Related

Convert Rows To Columns In A Table?

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

Converting Query Rows Into Columns?

Sep 14, 2008

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.

View 1 Replies View Related

Convert Rows To Multiple Columns

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

Displaying Rows Into Columns In A Table

Oct 20, 2012

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

View 1 Replies View Related

SQL & PL/SQL :: How To Convert Query Columns Into Rows

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

SQL & PL/SQL :: Pivoting Data (rows To Columns)

Aug 9, 2011

I have a table like below-

create table test (cust_id number(3), m_no number(1), other_date varchar2(20));

Where there is a "cust_id" that can have maximum 3 "m_no" (Don't bother for that condition, we have it in actual table).

In this table records are as follows.

insert into test values (1,1,'ABC');
insert into test values (1,2,'DEF');
insert into test values (2,1,'DEG');
insert into test values (3,2,'DEG');

[Code]..

My objective is to get date like below--

cust_idf_m_nof_oth_datas_m_nos_oth_datat_m_not_oth_data

1 1 ABC 2 DEF
2 1 DEG
3 2 DEG 1 BCD 3 MNO
4 9 KLM
5 2 XYZ 5 XAZ

I want to say, that cust_id's that doesn't have second and third m_no should not have any data in those columns.

Can this be performed through single query, or I need to make temporary tables or functions for this.

View 7 Replies View Related

SQL & PL/SQL :: Converting Rows To Columns Dynamically?

Oct 20, 2011

I Have a table like this. 12 rows and 8 columns.And column 1 Dt is going to change everytime,as date and months proceeds.and corresponding columns values are going to change.

I want to convert rows to columns dynamically..

DT COL1COL2COL3COL4COL5COL6 COL7
01/01/20111234567
01/02/2011131415161718 20
01/03/2011252627282930 32
01/04/201137383940414201/05/2011495051525354 55
01/06/2011616263646566 67
01/07/2011737475767778 29
01/08/2011858687888990 92
01/09/2011979899100101102 04
01/10/2011109110111112113114 115

i want result like this.

01/01/201101/02/201101/03/201101/04/2011
1 9 11 12
2 11 23 12
3 11 11 11
4
5
6
7
8

View 2 Replies View Related

SQL & PL/SQL :: Split Multiple Columns Into Rows

May 2, 2012

I have data like :

ID NAME1 NAME2 NAME3
JJ AD MED
VI TIBO PH TIBO

I want output like

ID NAME
JJ AD
JJ MED
VI TIBO
VI PH
VI TIBO

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved