Printing Records Horizontally


         by: Joseph Fuda,
             Technical Support Representative

         April 8, 1989


         Information is not always needed in the "one record under
         the other" format that SQL*Plus uses.  Sometimes lists must
         be presented horizontally for space considerations.  The
         following SQL*Plus statements show how information from one
         column can be printed horizontally across the screen.  The
         ENAME column of the EMP table is used for these examples.


         1) SET HEADING OFF

            SELECT MAX( DECODE( rownum,1,'Employee Name:', NULL )) title,
                   MAX( DECODE( MOD(rownum-1,3), 0, ename, NULL )) col1,
                   MAX( DECODE( MOD(rownum-1,3), 1, ename, NULL )) col2,
                   MAX( DECODE( MOD(rownum-1,3), 2, ename, NULL )) col3
            FROM   emp
            GROUP BY trunc( (rownum-1)/3 )

            Employee Name:  SMITH      ALLEN      WARD
                            JONES      MARTIN     BLAKE
                            CLARK      SCOTT      KING
                            TURNER     ADAMS      JAMES
                            FORD       MILLER


         2) To see how the SELECT statement works examine the results
            of the following statement.

            SELECT rownum-1 row,
                   mod(rownum-1,3) mod(row,3),
                   trunc((rownum-1)/3) trunc(row/3),
                   DECODE( MOD(rownum-1,3), 0, ename, NULL )) col1,
                   DECODE( MOD(rownum-1,3), 1, ename, NULL )) col2,
                   DECODE( MOD(rownum-1,3), 2, ename, NULL )) col3
            FROM emp

            ROW MOD(ROW,3) TRUNC(ROW/3) COL1       COL2       COL3
            --- ---------- ------------ -------    -------    -------
              0          0            0 SMITH
              1          1            0            ALLEN
              2          2            0                       WARD
              3          0            1 JONES
              4          1            1            MARTIN
              5          2            1                       BLAKE
              6          0            2 CLARK
              7          1            2            SCOTT
              8          2            2                       KING
              9          0            3 TURNER
              10         1            3            ADAMS
              11         2            3                       JAMES
              12         0            4 FORD
              13         1            4            MILLER


         3) By using the MAX function and grouping by TRUNC( (rownum-1)/3 )
            the following result is obtained.

            SELECT MAX( DECODE( MOD(rownum-1,3), 0, ename, NULL )) col1,
                   MAX( DECODE( MOD(rownum-1,3), 1, ename, NULL )) col2,
                   MAX( DECODE( MOD(rownum-1,3), 2, ename, NULL )) col3
            FROM   emp
            GROUP BY TRUNC( (rownum-1)/3 )

            COL1       COL2       COL3
            -------    -------    -------
            SMITH      ALLEN      WARD
            JONES      MARTIN     BLAKE
            CLARK      SCOTT      KING
            TURNER     ADAMS      JAMES
            FORD       MILLER


         4) To make the report look cleaner, the heading "Employee Names: "
            is added to the first record of a new column called "title"
            and the SQL*Plus column headings are turned off with the SET
            command.

            SET HEADING OFF

            SELECT MAX( DECODE( rownum,1,'Employee Name:', NULL )) title,
                   MAX( DECODE( MOD(rownum-1,3), 0, ename, NULL )) col1,
                   MAX( DECODE( MOD(rownum-1,3), 1, ename, NULL )) col2,
                   MAX( DECODE( MOD(rownum-1,3), 2, ename, NULL )) col3
            FROM   emp
            GROUP BY trunc( (rownum-1)/3 )

            Employee Name:  SMITH      ALLEN      WARD
                            JONES      MARTIN     BLAKE
                            CLARK      SCOTT      KING
                            TURNER     ADAMS      JAMES
                            FORD       MILLER


         5) The report can be easily expanded to four columns by
            using the following statement.


            SELECT MAX( DECODE( rownum,1,'Employee Name:', NULL )) title,
                   MAX( DECODE( MOD(rownum-1,4), 0, ename, NULL )) col1,
                   MAX( DECODE( MOD(rownum-1,4), 1, ename, NULL )) col2,
                   MAX( DECODE( MOD(rownum-1,4), 2, ename, NULL )) col3,
                   MAX( DECODE( MOD(rownum-1,4), 3, ename, NULL )) col4
            FROM   emp
            GROUP BY trunc( (rownum-1)/4 )

            Employee Name:  SMITH      ALLEN      WARD       JONES
                            MARTIN     BLAKE      CLARK      SCOTT
                            KING       TURNER     ADAMS      JAMES
                            FORD       MILLER



© Copyright Joseph Fuda 1989