Convert columns into row of a table

User_40B57

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));

mathguy

WHY do you need to do that? What's the point of showing employees lined up in rows and columns like that?

What will you do with a department with 40 employees? Will your output really have 40 columns? Who will read such a report? (I say "read" because there is no other possible, sane use for such an output; you can't use it for any further processing.)

Of course, I am not answering your question here; I am only saying that one should not ask that question in the first place.

Frank Kulash

Hi, @User_40B57

Do you really need a separate column for each employee, or would you be satisfied with a big string that was formatted to look like separate columns?

For the former, you need dynamic SQL.

For the latter, see PIVOT SQL — oracle-tech

If you'd like help, post INSERT statements for a little sample data (to go with the CREATE TABLE statement you already posted), the exact results you want from that sample data (if not already posted), any special requirements you have (e.g., if the employees must be in a specific order, or how to handle dulpicates) and your best attempt at a solution

Solomon Yakobson

Use SQL macro (assuming you are on 19C or later:

CREATE OR REPLACE
  FUNCTION EMP_BY_DEPT_MACRO
    RETURN CLOB SQL_MACRO
    IS
        V_EMP_COL_COUNT NUMBER;
        V_SQL           CLOB := '
WITH T
  AS (
      SELECT  DEPTNO,
              ENAME,
              ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME) RN
        FROM  EMP
     )
SELECT  DEPTNO';
    BEGIN
        SELECT  MAX(COUNT(*))
          INTO  V_EMP_COL_COUNT
          FROM  EMP
          GROUP BY DEPTNO;
        FOR V_I IN 1..V_EMP_COL_COUNT LOOP
          V_SQL := V_SQL || ',MAX(CASE RN WHEN ' || V_I || ' THEN ENAME END) EMP' || V_I;
        END LOOP;
        V_SQL := V_SQL || ' FROM T GROUP BY DEPTNO';
        RETURN V_SQL;
END;
/

Function created.

SQL> SELECT  *
  2    FROM  TABLE(EMP_BY_DEPT_MACRO)
  3  /

    DEPTNO EMP1       EMP2       EMP3       EMP4       EMP5       EMP6
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 CLARK      KING       MILLER
        20 ADAMS      FORD       JONES      SCOTT      SMITH
        30 ALLEN      BLAKE      JAMES      MARTIN     TURNER     WARD

SQL>

SY.

User_F8W33

select dno,listagg(ename,',') within group (order by ename) as employee from emp group by dno order by dno;

User_H3J7U

https://community.oracle.com/tech/developers/discussion/comment/16792738#Comment_16792738

What happens if you add/delete row in department with the maximum employees??

Solomon Yakobson

What happens if you add/delete row in department with the maximum employees??

Yes, I should have mentioned this. SQL macro same as ODCI table interface (and most likely SQL Macro is built on top of it) has major flaw - select list expression count and their types are determined at hard parse time. So yes, number of select list expressions will not change next time unless we force hard:

SQL> SELECT  *

  2    FROM  TABLE(EMP_BY_DEPT_MACRO)
  3  /

    DEPTNO EMP1       EMP2       EMP3       EMP4       EMP5       EMP6
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 CLARK      KING       MILLER
        20 ADAMS      FORD       JONES      SCOTT      SMITH
        30 ALLEN      BLAKE      JAMES      MARTIN     TURNER     WARD

SQL> INSERT
  2    INTO EMP(EMPNO,DEPTNO,ENAME)
  3    VALUES(1,30,'DUMMY')
  4  /

1 row created.

SQL> SELECT  *
  2    FROM  TABLE(EMP_BY_DEPT_MACRO)
  3  /

    DEPTNO EMP1       EMP2       EMP3       EMP4       EMP5       EMP6
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 CLARK      KING       MILLER
        20 ADAMS      FORD       JONES      SCOTT      SMITH
        30 ALLEN      BLAKE      DUMMY      JAMES      MARTIN     TURNER

SQL>      

As you can see department 30 has now 7 employees but number of select list expressions didn't change and as a result WARD who alphabetically became seventh employee didn't show up since DUMMY who is alphabetically third pushed WARD out. We need to force hard parse by constructing different (by at least one character) SQL. SQL*Plus example:

SQL> SET VERIFY OFF
SQL> COLUMN HARD_PARSE_ENFORCER NEW_VALUE HARD_PARSE_ENFORCER NOPRINT
SQL> SELECT  SYS_GUID() HARD_PARSE_ENFORCER
  2    FROM  DUAL
  3  /

SQL> SELECT  *
  2    FROM  TABLE(EMP_BY_DEPT_MACRO) -- &&HARD_PARSE_ENFORCER
  3  /

    DEPTNO EMP1       EMP2       EMP3       EMP4       EMP5       EMP6
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 CLARK      KING       MILLER
        20 ADAMS      FORD       JONES      SCOTT      SMITH
        30 ALLEN      BLAKE      JAMES      MARTIN     TURNER     WARD

SQL> INSERT
  2    INTO EMP(EMPNO,DEPTNO,ENAME)
  3    VALUES(1,30,'DUMMY')
  4  /

1 row created.

SQL> SELECT  SYS_GUID() HARD_PARSE_ENFORCER
  2    FROM  DUAL
  3  /

SQL> SELECT  *
  2    FROM  TABLE(EMP_BY_DEPT_MACRO) -- &&HARD_PARSE_ENFORCER
  3  /

    DEPTNO EMP1       EMP2       EMP3       EMP4       EMP5       EMP6       EMP7
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 CLARK      KING       MILLER
        20 ADAMS      FORD       JONES      SCOTT      SMITH
        30 ALLEN      BLAKE      DUMMY      JAMES      MARTIN     TURNER     WARD

SQL> DELETE EMP
  2    WHERE ENAME = 'DUMMY'
  3  /

1 row deleted.


SQL> SELECT  SYS_GUID() HARD_PARSE_ENFORCER
  2    FROM  DUAL
  3  /

SQL> SELECT  *
  2    FROM  TABLE(EMP_BY_DEPT_MACRO) -- &&HARD_PARSE_ENFORCER
  3  /

    DEPTNO EMP1       EMP2       EMP3       EMP4       EMP5       EMP6
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 CLARK      KING       MILLER
        20 ADAMS      FORD       JONES      SCOTT      SMITH
        30 ALLEN      BLAKE      JAMES      MARTIN     TURNER     WARD

SQL>

SY.

P.S. It would be very nice if SQL macro would support hard parse enforcement. For example something like PRAGMA ENFORCE_HARD_HARSE which would set hard parse flag for SQL macro function. Then when SQL is parsed first time and it finds out SQL macro with PRAGMA ENFORCE_HARD_HARSE is used it would set hard harse flag for that SQL_ID. Now soft parse can check that flag and initiate hard parse each time same SQL is executed. Obviously there will be some performance degradation resulting from using PRAGMA ENFORCE_HARD_HARSE but it will be user's choice and, IMHO, is better and cleaner than forcing hard parse by "faking" different statement using different comment each time (or similar, e.g. changing number of blanks between words).