Need to convert columns into row for the below mentioned table
Scripts:
CREATE TABLE OPTIM.EMP
(
EMP_NO NUMBER,
ENAME VARCHAR2(100 BYTE),
DNO NUMBER
);
select * from emp;
Query used to convert columns to row is as below:
SELECT dno, LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY dno ORDER BY dno;
Got output as below :
But my expected output should be as below:
I want my data to be displayed in separate cell (Highlighted in Green color).
Basically delimiter need to be replaced with next cell.
I have also tried with PIVOT it doesn't worked.
SELECT *
FROM (SELECT emp_no,ename,dno
FROM emp)
PIVOT (MAX(ename) FOR dno IN (30));