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

PL/SQL :: Sql Query To Convert Rows To Columns

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

JDeveloper, Java & XML :: Convert Some Of The Rows Into Columns Query

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

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

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

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

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

PL/SQL :: Convert Columns Into Rows In Oracle 10g?

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

Forms :: Convert Rows Into Columns As Tabular?

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

How To Convert Table Data From Rows To Columns

Mar 11, 2013

View 4 Replies View Related

SQL & PL/SQL :: Convert Rows To Columns Using Pivot Method

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

PL/SQL :: How To Convert Rows To Columns Data In A Table

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

SQL & PL/SQL :: Convert Columns To Rows With Extra Hard Coded Column

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

SQL & PL/SQL :: Query To Convert Rows Into Column

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

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

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

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

SQL & PL/SQL :: Construct Query To Fetch Different Rows From Same Table In Different Columns

May 25, 2013

Lets say I have a table in ORACLE database like:

ACC_ID | ACC_AMT
111 | 10000
111 | 12000
111 | 14000
222 | 25000
222 | 30000
333 | 18000
333 | 27000
333 | 13000
333 | 15000

I want to get the output as:

ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
111 | 10000 | 222 | 25000 | 333 | 18000
111 | 12000 | 222 | 30000 | 333 | 27000
111 | 14000 | null | null | 333 | 13000
null | null | null | null | 333 | 15000

I need each different ACC_ID with ACC_AMT in different columns. The table may have other different ACC_ID also, but I will fetch only what I need. What is the best way to do this?

So far I have tried this:

SELECT
(CASE WHEN ACC_ID=111 THEN ACC_ID END) AS ACC_ID_1,
(CASE WHEN ACC_ID=111 THEN ACC_AMT END) AS ACC_AMT_1,
(CASE WHEN ACC_ID=222 THEN ACC_ID END) AS ACC_ID_2,
(CASE WHEN ACC_ID=222 THEN ACC_AMT END) AS ACC_AMT_2,
(CASE WHEN ACC_ID=333 THEN ACC_ID END) AS ACC_ID_3,
(CASE WHEN ACC_ID=333 THEN ACC_AMT END) AS ACC_AMT_3
FROM <TABLE_NAME>

But I am not getting the desired result.

View 22 Replies View Related

SQL & PL/SQL :: Query To Transpose Quest_id And Ans_id Columns Into Rows For Matching Records

Nov 16, 2012

create table ref_tbl (col1 varchar2(10),
col2 varchar2(10),
QUEST_ID_1 varchar2(10),
ANS_ID_1 varchar2(10),
QUEST_ID_2 varchar2(10),
ANS_ID_2 varchar2(10)
);
[code]...

Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records...my output should be like

CUS_IDCOL1COL2QUEST_IDANS_ID
------------------------------------------------
1PMP1234Q101A1234
1PMP1234Q102A2345
1STR2345Q201A2234
2PMP1234Q101A1234
2PMP1234Q102A2345
2STR2345Q201A2234

View 11 Replies View Related

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

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 View Related

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

Jan 21, 2011

I have a two question.

Question 1:How to select all columns from table except those columns which i type in query

Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data

View 5 Replies View Related

SQL & PL/SQL :: Convert Object Return Type Columns

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

SQL & PL/SQL :: Convert Many Rows To One

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

SQL & PL/SQL :: Split Is A Pipe Line Function To Convert Row As Columns

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

SQL & PL/SQL :: How To Convert Rows Into Column

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