Convert Rows to Columns [message #652271] |
Sun, 05 June 2016 23:00 |
|
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 |
|
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 #652279 is a reply to message #652274] |
Mon, 06 June 2016 01:26 |
|
Barbara Boehmer
Messages: 9090 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 #652295 is a reply to message #652289] |
Mon, 06 June 2016 08:21 |
|
Michel Cadot
Messages: 68647 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.
|
|
|