SQL

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

SQL

Postby chatwidravi » Thu Aug 08, 2013 8:48 am

HI,
Everybody what would be the query for the below condition:-

I HAVE A TABLE CALLED X IN WHICH I HAVE ONE COLUMN CALLED A AND THE VALUES ARE LIKE
1
2
3
4
5
6
7
8
9
10
BUT I WANT THE VALUES OF COLUMN IN ROWS LIKE
A1 A2 A3 A4 A5 A6 A7 A8 A9 A10
1 2 3 4 5 6 7 8 9 10
chatwidravi
Member
 
Posts: 5
Joined: Thu Aug 08, 2013 8:28 am

Re: SQL

Postby Tim... » Thu Aug 08, 2013 10:45 am

Hi.

You can do that with the PIVOT operator. This article discusses it.

http://www.oracle-base.com/articles/11g ... -11gr1.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: SQL

Postby chatwidravi » Thu Aug 08, 2013 11:29 am

Hi Tim
I tried to execute the query
(SELECT *
FROM (SELECT A
FROM X)
PIVOT (SUM(A) AS New FOR (A) IN (1,2,3,4,5,6,7,8,9,10))
ORDER BY A;)

using pivot function but it is showing error like
ORA-00933:SQL command not properly ended
chatwidravi
Member
 
Posts: 5
Joined: Thu Aug 08, 2013 8:28 am

Re: SQL

Postby Tim... » Thu Aug 08, 2013 11:37 am

Hi.

This works fine for me.

Code: Select all
CREATE TABLE t1 AS
SELECT level AS id
FROM   dual
CONNECT BY level <= 10;

SELECT *
FROM   (SELECT id
        FROM   t1)
PIVOT  (SUM(id) AS id FOR (id) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10));


I'm not sure what you are expecting your ORDER BY to do. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: SQL

Postby chatwidravi » Thu Aug 08, 2013 11:59 am

Hi Tim,
Thanks a lot now the query is working fine
chatwidravi
Member
 
Posts: 5
Joined: Thu Aug 08, 2013 8:28 am

Re: SQL

Postby Tim... » Thu Aug 08, 2013 4:38 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: sellyh19 and 3 guests