top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

How to convert Oracle rows to columns?

0 votes
124 views
How to convert Oracle rows to columns?
posted Aug 21, 2014 by Selvimohana

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

3 Answers

0 votes

The function PIVOT transposes rows in columns and the function UNPIVOT transposes columns in rows. They have been added in 11g. You can try that.

Source: http://tech.queryhome.com/55253/how-can-i-convert-the-columns-into-rows-in-oracle

answer Aug 21, 2014 by Salil Agrawal
0 votes

select
deptno,
rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
from
emp
group by
deptno
;

answer Sep 1, 2014 by Rathi
0 votes

Pivot provides an easy mechanism in Sql Server to transform rows into columns.

-- Creating Test Table
CREATE TABLE Example(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Example(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Example(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Example(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Example(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Example(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Example(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Example(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO

-- Selecting and checking entires in table
SELECT * FROM Example
GO

-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Example) up
PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO
answer Nov 17, 2014 by Manikandan J
Similar Questions
0 votes

I have a requirement to convert the columns of a table into Rows?

Ex

Table T1

C1     C2
X         Y
A         B

The output should be

X     A
Y     B

Help me to sort out this issue.

+1 vote

Do i need to write in PL/SQL or is it possible in SQL query?

+1 vote

declare
lc_status:='XXstatus';
Begin
Update xxtable
set Status_fg=lc_status
where con_bill_id=1234;

-- here i need to display how rows were updated.

end;

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...