How to display column values in row using sql

There could be different way of displaying column values in a row.

First One:

SELECT LISTAGG(last_name, '; ')
         WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
       MIN(hire_date) "Earliest"
  FROM employees
  WHERE department_id = 30;

Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

Second one which is also known as Pivoting:

SQL> SELECT UPPER(o.order_mode) order_mode

  2   ,SUM(DECODE(o.order_status, 0, o.order_total)) stat_0

  3   ,SUM(DECODE(o.order_status, 1, o.order_total)) stat_1

  4   ,SUM(DECODE(o.order_status, 2, o.order_total)) stat_2

  5   ,SUM(DECODE(o.order_status, 3, o.order_total)) stat_3

  6   ,SUM(DECODE(o.order_status, 4, o.order_total)) stat_4

  7   ,SUM(DECODE(o.order_status, 5, o.order_total)) stat_5

  8   ,SUM(DECODE(o.order_status, 6, o.order_total)) stat_6

  9   ,SUM(DECODE(o.order_status, 7, o.order_total)) stat_7

 10   ,SUM(DECODE(o.order_status, 8, o.order_total)) stat_8

 11   ,SUM(DECODE(o.order_status, 9, o.order_total)) stat_9

 12   ,SUM(DECODE(o.order_status, 10, o.order_total)) stat_10

 13  FROM orders o

 14  GROUP BY o.order_mode

 15  ORDER BY 1

 16  /

ORDER_MODE     STAT_0     STAT_1     STAT_2     STAT_3     STAT_4     STAT_5     STAT_6     STAT_7     STAT_8     STAT_9    STAT_10

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

DIRECT       163131.3   227569.5   166169.5   206659.4    56352.5   172586.2     115968    33617.1   545300.5   205674.2      10601

ONLINE        25976.7              103834.4    56381.7   700068.1   183261.2    90411.8              322192.5    57062.4   225236.7

1

Our first example will be a simple demonstration of the PIVOT syntax. Using the EMP table, we will sum the salaries by department and job, but transpose the sum for each department onto its own column. Before we pivot the salaries, we will examine the base data, as follows.

SQL> SELECT job

  2  ,      deptno

  3  ,      SUM(sal) AS sum_sal

  4  FROM   emp

  5  GROUP  BY

  6         job

  7  ,      deptno

  8  ORDER  BY

  9         job

 10  ,      deptno;

JOB           DEPTNO    SUM_SAL

--------- ---------- ----------

ANALYST           20       6600

CLERK             10       1430

CLERK             20       2090

CLERK             30       1045

MANAGER           10       2695

MANAGER           20     3272.5

MANAGER           30       3135

PRESIDENT         10       5500

SALESMAN          30       6160

9 rows selected.

We will now pivot this data using the new 11g syntax. For each job, we will display the salary totals in a separate column for each department, as follows.

SQL> WITH pivot_data AS (

  2          SELECT deptno, job, sal

  3          FROM   emp

  4          )

  5  SELECT *

  6  FROM   pivot_data

  7  PIVOT (

  8             SUM(sal)        --<-- pivot_clause

  9         FOR deptno          --<-- pivot_for_clause

 10         IN  (10,20,30,40)   --<-- pivot_in_clause

 11        );

JOB               10         20         30         40

--------- ---------- ---------- ---------- ----------

CLERK           1430       2090       1045

SALESMAN                              6160

PRESIDENT       5500

MANAGER         2695     3272.5       3135

ANALYST                    6600

5 rows selected.83261.2    90411.8              322192.5