Home » SQL & PL/SQL » SQL & PL/SQL » Convert Rows to Columns (Oracle 11g)
Convert Rows to Columns [message #652271] Sun, 05 June 2016 23:00 Go to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
Hi All,

Please find table script for sample data
CREATE TABLE N1 AS
(SELECT '1' AS COL FROM DUAL
UNION ALL
SELECT 'XYZ' AS COL FROM DUAL
UNION ALL
SELECT '1000' AS COL FROM DUAL
UNION ALL
SELECT 'SALES' AS COL FROM DUAL
UNION ALL
SELECT '2' AS COL FROM DUAL
UNION ALL
SELECT 'ABC' AS COL FROM DUAL
UNION ALL
SELECT '2000' AS COL FROM DUAL
UNION ALL
SELECT 'PROD' AS COL FROM DUAL);

I am trying to convert Rows to Columns using PIVOT but not able to achieve.

Input Data
Col_Name
1
ABC
1000
SALES
2
XYZ
2000
PROD

Output Needed
ID       ENAME       SAL       DEPT
1        ABC         1000      SALES
2        XYZ         2000      PROD

I am writing this query but not able to get above output.

SELECT * FROM
(SELECT N1.*, MOD(ROWNUM,4) AS RNO
  FROM N1)
PIVOT
(COUNT(COL)
 FOR COL IN (1,2,3,0)
);
Re: Convert Rows to Columns [message #652274 is a reply to message #652271] Mon, 06 June 2016 00:12 Go to previous messageGo to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
Hi,

I have come up with this solution but looking for better solution with PIVOT or enhancement in this code itself.

SELECT 
MAX(DECODE(RANK,1,COL)) AS EMPNO,
MAX(DECODE(RANK,2,COL)) AS ENAME,
MAX(DECODE(RANK,3,COL)) AS SALARY,
MAX(DECODE(RANK,4,COL)) AS DEPT
FROM
(SELECT ROWNUM AS RANK, N1.* FROM N1)
UNION
SELECT 
MAX(DECODE(RANK,5,COL)) AS EMPNO,
MAX(DECODE(RANK,6,COL)) AS ENAME,
MAX(DECODE(RANK,7,COL)) AS SALARY,
MAX(DECODE(RANK,8,COL)) AS DEPT
FROM
(SELECT ROWNUM AS RANK, N1.* FROM N1);
Re: Convert Rows to Columns [message #652278 is a reply to message #652274] Mon, 06 June 2016 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your solution is wrong and if you have nothing in the table itself to tell the semantics of the value you can't do it.

Re: Convert Rows to Columns [message #652279 is a reply to message #652274] Mon, 06 June 2016 01:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You need something to order the columns by. The following uses ROWNUM, which works only because the values are ordered within a select statement that uses UNION ALL. If you use that same select to insert into a table, then select from that table, without a column to order by, there is no guarantee that the order will be the same and you may get any value in any row of any column. So, if this is just a simulation and n1 is some ordered sub-query, then it will work, but not if you are just selecting these columns from a table.

SCOTT@orcl_12.1.0.2.0> WITH
  2    N1 AS
  3  	 (SELECT '1' AS COL FROM DUAL
  4  	  UNION ALL
  5  	  SELECT 'XYZ' AS COL FROM DUAL
  6  	  UNION ALL
  7  	  SELECT '1000' AS COL FROM DUAL
  8  	  UNION ALL
  9  	  SELECT 'SALES' AS COL FROM DUAL
 10  	  UNION ALL
 11  	  SELECT '2' AS COL FROM DUAL
 12  	  UNION ALL
 13  	  SELECT 'ABC' AS COL FROM DUAL
 14  	  UNION ALL
 15  	  SELECT '2000' AS COL FROM DUAL
 16  	  UNION ALL
 17  	  SELECT 'PROD' AS COL FROM DUAL)
 18  SELECT empno, ename, salary, dept
 19  FROM   (SELECT CEIL (ROWNUM / 4) rn, MOD (ROWNUM, 4) AS rno, col
 20  	     FROM   n1)
 21  PIVOT  (MAX (col) FOR rno IN (1 AS empno, 2 AS ename, 3 AS salary, 0 AS dept))
 22  /

EMPNO ENAME SALAR DEPT
----- ----- ----- -----
1     XYZ   1000  SALES
2     ABC   2000  PROD

2 rows selected.
Re: Convert Rows to Columns [message #652282 is a reply to message #652279] Mon, 06 June 2016 02:44 Go to previous messageGo to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
Thanks Barbara for the reply.
Re: Convert Rows to Columns [message #652284 is a reply to message #652282] Mon, 06 June 2016 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And no thanks for me?

Re: Convert Rows to Columns [message #652286 is a reply to message #652284] Mon, 06 June 2016 05:19 Go to previous messageGo to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
@Michel: You are Super Champ and my Idol. You are truly amazing. So if i say n nos of times thanks still that's less for my champ.
Re: Convert Rows to Columns [message #652287 is a reply to message #652286] Mon, 06 June 2016 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know why but I feel you are not really sincere.

So what about our remarks about you can't get any actual solution to your question?

Re: Convert Rows to Columns [message #652289 is a reply to message #652287] Mon, 06 June 2016 07:08 Go to previous messageGo to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
If i have would not been sincere then i would not have re-posted my post after you removed my post.

Coming to learning, yes i am learning slowly by the suggestion provided by all of you. I m not from computers background. I an a Sales person because of job crisis i need to learn that's why i am into this field. So everything is newbie.
Re: Convert Rows to Columns [message #652295 is a reply to message #652289] Mon, 06 June 2016 08:21 Go to previous message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My question is did you understand what we told you and why you can't have an actual solution to your question if you have not something else, another column for instance, that indicates what I call the semantics and what Barbara called the order of the values?
In short, I'm inquiring if you have actually learned from this topic or if something has to added.

Previous Topic: Adding Cells from two different tables
Next Topic: union all and sql*plus
Goto Forum:
  


Current Time: Sun Aug 01 19:14:49 CDT 2021