Sunday, 10 February 2013

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