Forum Stats

  • 3,759,253 Users
  • 2,251,515 Discussions
  • 7,870,551 Comments

Discussions

Convert columns into row of a table

User_40B57
User_40B57 Member Posts: 7 Green Ribbon

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

Tagged:

Answers

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond

    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
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond

    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
    Solomon Yakobson Member Posts: 18,743 Red Diamond

    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_WI23P
  • User_F8W33
    User_F8W33 Member Posts: 18 Blue Ribbon

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

  • User_H3J7U
    User_H3J7U Member Posts: 485 Bronze Trophy

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

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,743 Red Diamond
    edited May 11, 2021 11:39AM

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