top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to convert Oracle rows to columns?

0 votes
631 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;

...