Display Multiple Column Values As A Single Column Values

There Can Be A Scenario Where You want To Display All The Column Values Into A Single Result Column Rows.

U can achieve this by using ROLLUP command in ORACLE.

example:-

----------

create table empdetails (empno number,ename  varchar2(100),mgrname  varchar2(20))

Sql> insert into empdetails values (1,'Smith','Allen');

Sql> insert into empdetails values (2,'Math','Jhon');

Sql> insert into empdetails values (3,'Andrew',null);

Sql> Commit;

In the case when u want to display output like below

EMP_DIST_ROWS

1

2

3

Allen

Andrew

Jhon

Math

Smith

Using below query to get the above result:

Sql>SELECT DISTINCT emp_dist_rows

           FROM (SELECT   DECODE (mgrname,

                                  NULL, NVL (ename, empno),

                                  mgrname

                                 ) emp_dist_rows

                     FROM empdetails

                 GROUP BY ROLLUP (empno, ename, mgrname))

          WHERE emp_dist_rows IS NOT NULL;

EMP_DIST_ROWS

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

1

2

3

Allen

Andrew

Jhon

Math

Smith

Here null values are eliminated by using distinct keyword.

If you want exact count of column values then use below query( i.e without distinct keyword)

Sql> SELECT count(emp_dist_rows)

           FROM (SELECT   DECODE (mgrname,

                                  NULL, NVL (ename, empno),

                                  mgrname

                                 ) emp_dist_rows

                     FROM empdetails

                 GROUP BY ROLLUP (empno, ename, mgrname))

          WHERE emp_dist_rows IS NOT NULL