OraFAQ Forum: SQL & PL/SQL » How to view the rows in the table format using select query

Home » SQL & PL/SQL » SQL & PL/SQL » How to view the rows in the table format using select query (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to view the rows in the table format using select query [message #555767]

Mon, 28 May 2012 10:27

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

Hi,

I need to view the rows of the result of a select query in table format in GUI application.

I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo

SELECT   RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
              ',')
          AS empid,
         RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
              ',')
          AS "employee name" ,
         RTRIM (XMLAGG (XMLELEMENT (e, b.DEPTNO || ',')).EXTRACT ('//text()'),
              ',') as  depid
  FROM emp e,dept b
  WHERE e.DEPTNO = b.DEPTNO
  group by b.DEPTNO ;

EMPID				employee name				DEPID
778,278,397,934			CLARK,MILLER,KING			10,10,10
73,697,566,790,278,700,000	SMITH,FORD,ADAMS,JONES,SCOTT		20,20,20,20,20
749,976,547,900,784,000,000,000	ALLEN,WARD,MARTIN,TURNER,JAMES,BLAKE	30,30,30,30,30,30

But the case is I need to display the value in table format Horizontally as below

EMPID				employee name				DEPID
778
278				CLARK					10	
397				MILLER					
934				KING					
									
732				SMITH					20
697				FORD					
566				ADAMS					
790				JONES

Please let me know the query to make this case

Thanks in advance,

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555768 is a reply to message #555767]

Mon, 28 May 2012 10:44

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use BREAK command:

SQL> break on deptno skip 1 nodup
SQL> select empno, ename, deptno from emp order by deptno, empno;
     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7782 CLARK              10
      7839 KING
      7934 MILLER

      7369 SMITH              20
      7566 JONES
      7788 SCOTT
      7876 ADAMS
      7902 FORD

      7499 ALLEN              30
      7521 WARD
      7654 MARTIN
      7698 BLAKE
      7844 TURNER
      7900 JAMES

Regards
Michel

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555769 is a reply to message #555767]

Mon, 28 May 2012 11:50

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

If I understand correctly, you are saying that the query that you provided is to simulate the format of your data for testing purposes, something like this:

SCOTT@orcl_11gR2> COLUMN empid		 FORMAT A29
SCOTT@orcl_11gR2> COLUMN "employee name" FORMAT A36
SCOTT@orcl_11gR2> COLUMN depid		 FORMAT A17
SCOTT@orcl_11gR2> WITH
  2    data AS
  3  	 (SELECT   RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
  4  			',')
  5  		    AS empid,
  6  		   RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
  7  			',')
  8  		    AS "employee name" ,
  9  		   RTRIM (XMLAGG (XMLELEMENT (e, b.DEPTNO || ',')).EXTRACT ('//text()'),
 10  			',') as  depid
 11  	    FROM emp e,dept b
 12  	    WHERE e.DEPTNO = b.DEPTNO
 13  	    group by b.DEPTNO)
 14  SELECT * FROM data
 15  /

EMPID                         employee name                        DEPID
----------------------------- ------------------------------------ -----------------
7782,7934,7839                CLARK,MILLER,KING                    10,10,10
7369,7902,7876,7788,7566      SMITH,FORD,ADAMS,SCOTT,JONES         20,20,20,20,20
7499,7900,7844,7698,7654,7521 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD 30,30,30,30,30,30

3 rows selected.

Then you want a query that selects from that data, resulting in output in the desired format, something like this:

SCOTT@orcl_11gR2> WITH
  2    data AS
  3  	 (SELECT   RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
  4  			',')
  5  		    AS empid,
  6  		   RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
  7  			',')
  8  		    AS "employee name" ,
  9  		   RTRIM (XMLAGG (XMLELEMENT (e, b.DEPTNO || ',')).EXTRACT ('//text()'),
 10  			',') as  depid
 11  	    FROM emp e,dept b
 12  	    WHERE e.DEPTNO = b.DEPTNO
 13  	    group by b.DEPTNO)
 14  SELECT SUBSTR
 15  	      (empid,
 16  	       INSTR (',' || empid, ',', 1, COLUMN_VALUE),
 17  	       INSTR (empid || ',', ',', 1, COLUMN_VALUE)
 18  	       - INSTR (',' || empid, ',', 1, COLUMN_VALUE)) empid,
 19  	    SUBSTR
 20  	      ("employee name",
 21  	       INSTR (',' || "employee name", ',', 1, COLUMN_VALUE),
 22  	       INSTR ("employee name" || ',', ',', 1, COLUMN_VALUE)
 23  	       - INSTR (',' || "employee name", ',', 1, COLUMN_VALUE)) "employee name",
 24  	    DECODE
 25  	      (COLUMN_VALUE,
 26  	       1,
 27  	       SUBSTR
 28  		 (depid,
 29  		  INSTR (' ' || depid, ',', 1, COLUMN_VALUE),
 30  		  INSTR (depid || ',', ',', 1, COLUMN_VALUE)
 31  		  - INSTR (',' || depid, ',', 1, COLUMN_VALUE)),
 32  	       NULL) depid
 33  FROM   data,
 34  	    TABLE
 35  	      (CAST
 36  		 (MULTISET
 37  		    (SELECT LEVEL
 38  		     FROM   DUAL
 39  		     CONNECT BY LEVEL <= REGEXP_COUNT (empid, ',') + 1)
 40  		  AS SYS.ODCIVARCHAR2LIST))
 41  /

EMPID                         employee name                        DEPID
----------------------------- ------------------------------------ -----------------
7782                          CLARK                                10
7934                          MILLER
7839                          KING
7369                          SMITH                                20
7902                          FORD
7876                          ADAMS
7788                          SCOTT
7566                          JONES
7499                          ALLEN                                30
7900                          JAMES
7844                          TURNER
7698                          BLAKE
7654                          MARTIN
7521                          WARD

14 rows selected.

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555789 is a reply to message #555769]

Tue, 29 May 2012 00:22

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

hi,

FYI..,
I am using oracle 10g , REGEXP_COUNT can't executed.

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555796 is a reply to message #555789]

Tue, 29 May 2012 01:03

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use the old "length(string)-length(string,',')" instead.

Regards
Michel

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555857 is a reply to message #555789]

Tue, 29 May 2012 08:32

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

ram anand wrote on Mon, 28 May 2012 22:22

hi,

FYI..,
I am using oracle 10g , REGEXP_COUNT can't executed.

Sorry, I didn't notice. The following should work on 10g.

SCOTT@orcl_11gR2> WITH
  2    data AS
  3  	 (SELECT   RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
  4  			',')
  5  		    AS empid,
  6  		   RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
  7  			',')
  8  		    AS "employee name" ,
  9  		   RTRIM (XMLAGG (XMLELEMENT (e, b.DEPTNO || ',')).EXTRACT ('//text()'),
 10  			',') as  depid
 11  	    FROM emp e,dept b
 12  	    WHERE e.DEPTNO = b.DEPTNO
 13  	    group by b.DEPTNO)
 14  SELECT SUBSTR
 15  	      (empid,
 16  	       INSTR (',' || empid, ',', 1, COLUMN_VALUE),
 17  	       INSTR (empid || ',', ',', 1, COLUMN_VALUE)
 18  	       - INSTR (',' || empid, ',', 1, COLUMN_VALUE)) empid,
 19  	    SUBSTR
 20  	      ("employee name",
 21  	       INSTR (',' || "employee name", ',', 1, COLUMN_VALUE),
 22  	       INSTR ("employee name" || ',', ',', 1, COLUMN_VALUE)
 23  	       - INSTR (',' || "employee name", ',', 1, COLUMN_VALUE)) "employee name",
 24  	    DECODE
 25  	      (COLUMN_VALUE,
 26  	       1,
 27  	       SUBSTR
 28  		 (depid,
 29  		  INSTR (' ' || depid, ',', 1, COLUMN_VALUE),
 30  		  INSTR (depid || ',', ',', 1, COLUMN_VALUE)
 31  		  - INSTR (',' || depid, ',', 1, COLUMN_VALUE)),
 32  	       NULL) depid
 33  FROM   data,
 34  	    TABLE
 35  	      (CAST
 36  		 (MULTISET
 37  		    (SELECT LEVEL
 38  		     FROM   DUAL
 39  		     CONNECT BY LEVEL <= LENGTH (empid)
 40  		       - LENGTH (REPLACE (empid, ',', '')) + 1)
 41  		  AS SYS.ODCIVARCHAR2LIST))
 42  /

EMPID                         employee name                        DEPID
----------------------------- ------------------------------------ -----------------
7782                          CLARK                                10
7934                          MILLER
7839                          KING
7369                          SMITH                                20
7902                          FORD
7876                          ADAMS
7788                          SCOTT
7566                          JONES
7499                          ALLEN                                30
7900                          JAMES
7844                          TURNER
7698                          BLAKE
7654                          MARTIN
7521                          WARD

14 rows selected.

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555858 is a reply to message #555796]

Tue, 29 May 2012 08:40

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi,

I tried the above soultion, it was working pretty fine
i.e.

WITH
    data AS
       (SELECT   RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
              ',')
              AS empid,
             RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
              ',')
              AS "employee name" ,
             RTRIM (XMLAGG (XMLELEMENT (e, b.DEPTNO || ',')).EXTRACT ('//text()'),
              ',') as  depid
          FROM emp e,dept b
          WHERE e.DEPTNO = b.DEPTNO
          group by b.DEPTNO)
            SELECT SUBSTR
            (empid,
             INSTR (',' || empid, ',', 1, COLUMN_VALUE),
             INSTR (empid || ',', ',', 1, COLUMN_VALUE)
             - INSTR (',' || empid, ',', 1, COLUMN_VALUE)) empid,
          SUBSTR
            ("employee name",
             INSTR (',' || "employee name", ',', 1, COLUMN_VALUE),
             INSTR ("employee name" || ',', ',', 1, COLUMN_VALUE)
             - INSTR (',' || "employee name", ',', 1, COLUMN_VALUE)) "employee name",
          DECODE
            (COLUMN_VALUE,
             1,
             SUBSTR
           (depid,
            INSTR (' ' || depid, ',', 1, COLUMN_VALUE),
            INSTR (depid || ',', ',', 1, COLUMN_VALUE)
            - INSTR (',' || depid, ',', 1, COLUMN_VALUE)),
             NULL) depid
  FROM   data,
          TABLE
            (CAST
           (MULTISET
              (SELECT LEVEL
               FROM   DUAL
               CONNECT BY LEVEL <= length(empid)-length(replace(empid,','))+1 )
            AS SYS.ODCIVARCHAR2LIST))

I thought I would repleace the TABLE function with normal sub query and get rid of COLUMN_VALUE column, but It failed with the following reason, I could not understand why it got failed.

WITH
    data AS
       (SELECT   RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
              ',')
              AS empid,
             RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
              ',')
              AS "employee name" ,
             RTRIM (XMLAGG (XMLELEMENT (e, b.DEPTNO || ',')).EXTRACT ('//text()'),
              ',') as  depid
          FROM emp e,dept b
          WHERE e.DEPTNO = b.DEPTNO
          group by b.DEPTNO)
            SELECT SUBSTR
            (empid,
             INSTR (',' || empid, ',', 1, hh.bb),
             INSTR (empid || ',', ',', 1, hh.bb)
             - INSTR (',' || empid, ',', 1, hh.bb)) empid,
          SUBSTR
            ("employee name",
             INSTR (',' || "employee name", ',', 1, hh.bb),
             INSTR ("employee name" || ',', ',', 1, hh.bb)
             - INSTR (',' || "employee name", ',', 1, hh.bb)) "employee name",
          DECODE
            (hh.bb,
             1,
             SUBSTR
           (depid,
            INSTR (' ' || depid, ',', 1, hh.bb),
            INSTR (depid || ',', ',', 1, hh.bb)
            - INSTR (',' || depid, ',', 1, hh.bb)),
             NULL) depid
  FROM   data, (SELECT LEVEL bb
               FROM   DUAL
               CONNECT BY LEVEL <= length(empid)-length(replace(data.empid,','))+1 ) "hh"

ORA-00904: "DATA"."EMPID": invalid identifier

We were able to refer the empid column in the TABLE function but why not in the sub query

Regards,
Pointers

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555859 is a reply to message #555858]

Tue, 29 May 2012 08:49

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

but It failed with the following reason, I could not understand why it got failed.

Because you cannot reference a column that is in another source (table) of the same FROM clause.

Regards
Michel

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555861 is a reply to message #555859]

Tue, 29 May 2012 08:52

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi Micheal,

Thank you very much for your email.

I got your point, but how about in the case of TABLE function in the above query, does it mean TABLE function can refer a column that is in another source (table) of the same FROM clause.
or
IS there any other reason behind it.

Regards,
Pointers

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555862 is a reply to message #555861]

Tue, 29 May 2012 08:54

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

but how about in the case of TABLE function in the above query, does it mean TABLE function can refer a column that is in another source (table) of the same FROM clause.

Yes.

Regards
Michel

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555863 is a reply to message #555862]

Tue, 29 May 2012 08:57

pointers
Messages: 451
Registered: May 2008

Senior Member

Ok Thank you very much.

Regards,
Pointers

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555888 is a reply to message #555857]

Tue, 29 May 2012 11:55

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

Sorry, to jump into this thread but I was wondering how you would sort the "name(s)" within dept(s)??

I have to admit, I don't really know how the code works but it seems to me if you order the data like this

WITH data AS
 (SELECT RTRIM(XMLAGG(XMLELEMENT(e, EMPNO || ',')).EXTRACT('//text()'), ',') AS empid,
         RTRIM(XMLAGG(XMLELEMENT(e, ENAME || ',')).EXTRACT('//text()'), ',') AS "employee name",
         RTRIM(XMLAGG(XMLELEMENT(e, DEPTNO || ',')).EXTRACT('//text()'), ',') AS depid
    FROM (SELECT e.empno, e.ename, b.deptno
            FROM emp e, dept b
           WHERE e.DEPTNO = b.DEPTNO
           ORDER BY b.deptno, e.ename) d
   GROUP BY DEPTNO)
SELECT * FROM data

WITH data AS
 (SELECT *
    FROM (SELECT RTRIM(XMLAGG(XMLELEMENT(e, EMPNO || ',')).EXTRACT('//text()'), ',') AS empid,
                 RTRIM(XMLAGG(XMLELEMENT(e, ENAME || ',')).EXTRACT('//text()'), ',') AS "employee name",
                 RTRIM(XMLAGG(XMLELEMENT(e, b.DEPTNO || ','))
                       .EXTRACT('//text()'), ',') AS depid
            FROM emp e, dept b
           WHERE e.DEPTNO = b.DEPTNO
           GROUP BY b.DEPTNO)
   ORDER BY 3, 2)
SELECT * FROM data

Results(I just did a copy - edit - paste)

7782  CLARK  10
7839  KING  
7934  MILLER  
7876  ADAMS  20
7902  FORD  
7566  JONES  
7788  SCOTT  
7369  SMITH  
7499  ALLEN  30
7698  BLAKE  
7900  JAMES  
7654  MARTIN  
7844  TURNER  
7521  WARD

it would do it but it doesn't. Can the output be sorted??

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555890 is a reply to message #555888]

Tue, 29 May 2012 12:18

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Note that the initial WITH clause was provided by the original poster to simulate the data to be selected from, so there may or may not be any control of ordering within the aggregate strings. Any ordering would have to be done after the unnesting, as demonstrated below.

SCOTT@orcl_11gR2> WITH
  2    data AS
  3  	 (SELECT   RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
  4  			',')
  5  		    AS empid,
  6  		   RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
  7  			',')
  8  		    AS "employee name" ,
  9  		   RTRIM (XMLAGG (XMLELEMENT (e, b.DEPTNO || ',')).EXTRACT ('//text()'),
 10  			',') as  depid
 11  	    FROM emp e,dept b
 12  	    WHERE e.DEPTNO = b.DEPTNO
 13  	    group by b.DEPTNO),
 14    table_format AS
 15  	 (SELECT SUBSTR
 16  		   (empid,
 17  		    INSTR (',' || empid, ',', 1, COLUMN_VALUE),
 18  		    INSTR (empid || ',', ',', 1, COLUMN_VALUE)
 19  		    - INSTR (',' || empid, ',', 1, COLUMN_VALUE)) empid,
 20  		 SUBSTR
 21  		   ("employee name",
 22  		    INSTR (',' || "employee name", ',', 1, COLUMN_VALUE),
 23  		    INSTR ("employee name" || ',', ',', 1, COLUMN_VALUE)
 24  		    - INSTR (',' || "employee name", ',', 1, COLUMN_VALUE)) "employee name",
 25  		 SUBSTR
 26  		   (depid,
 27  		    INSTR (' ' || depid, ',', 1, COLUMN_VALUE),
 28  		    INSTR (depid || ',', ',', 1, COLUMN_VALUE)
 29  		    - INSTR (',' || depid, ',', 1, COLUMN_VALUE)) depid
 30  	  FROM	 data,
 31  		 TABLE
 32  		   (CAST
 33  		      (MULTISET
 34  			 (SELECT LEVEL
 35  			  FROM	 DUAL
 36  			  CONNECT BY LEVEL <= LENGTH (empid)
 37  			    - LENGTH (REPLACE (empid, ',', '')) + 1)
 38  		       AS SYS.ODCIVARCHAR2LIST)))
 39  SELECT empid, "employee name",
 40  	    DECODE
 41  	      (ROW_NUMBER () OVER (PARTITION BY depid ORDER BY "employee name"),
 42  	       1, depid, NULL) depid
 43  FROM   table_format
 44  ORDER  BY table_format.depid, "employee name"
 45  /

EMPID                         employee name                        DEPID
----------------------------- ------------------------------------ -----------------
7782                          CLARK                                10
7839                          KING
7934                          MILLER
7876                          ADAMS                                20
7902                          FORD
7566                          JONES
7788                          SCOTT
7369                          SMITH
7499                          ALLEN                                30
7698                          BLAKE
7900                          JAMES
7654                          MARTIN
7844                          TURNER
7521                          WARD

14 rows selected.

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555891 is a reply to message #555890]

Tue, 29 May 2012 12:45

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

As a separate exercise from the original problem, if you wanted to order the data within the XML, then you could use the ORDER BY clause within the XMLAGG, as shown below.

SCOTT@orcl_11gR2> COLUMN empid		 FORMAT A29
SCOTT@orcl_11gR2> COLUMN "employee name" FORMAT A36
SCOTT@orcl_11gR2> COLUMN depid		 FORMAT A17
SCOTT@orcl_11gR2> WITH
  2    data AS
  3  	 (SELECT RTRIM
  4  		   (XMLAGG
  5  		      (XMLELEMENT (e, EMPNO || ',') ORDER BY b.deptno, ename).EXTRACT ('//text()'),
  6  		    ',') AS empid,
  7  		 RTRIM
  8  		   (XMLAGG
  9  		      (XMLELEMENT (e, ENAME || ',') ORDER BY b.deptno, ename).EXTRACT ('//text()'),
 10  		    ',') AS "employee name",
 11  		 RTRIM
 12  		   (XMLAGG
 13  		      (XMLELEMENT (e, b.DEPTNO || ',') ORDER BY b.deptno, ename).EXTRACT ('//text()'),
 14  		    ',') as  depid
 15  	  FROM	 emp e,dept b
 16  	  WHERE  e.DEPTNO = b.DEPTNO
 17  	  group  by b.DEPTNO)
 18  SELECT * FROM data
 19  /

EMPID                         employee name                        DEPID
----------------------------- ------------------------------------ -----------------
7782,7839,7934                CLARK,KING,MILLER                    10,10,10
7876,7902,7566,7788,7369      ADAMS,FORD,JONES,SCOTT,SMITH         20,20,20,20,20
7499,7698,7900,7654,7844,7521 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 30,30,30,30,30,30

3 rows selected.

SCOTT@orcl_11gR2> WITH
  2    data AS
  3  	 (SELECT RTRIM
  4  		   (XMLAGG
  5  		      (XMLELEMENT (e, EMPNO || ',') ORDER BY b.deptno, ename).EXTRACT ('//text()'),
  6  		    ',') AS empid,
  7  		 RTRIM
  8  		   (XMLAGG
  9  		      (XMLELEMENT (e, ENAME || ',') ORDER BY b.deptno, ename).EXTRACT ('//text()'),
 10  		    ',') AS "employee name",
 11  		 RTRIM
 12  		   (XMLAGG
 13  		      (XMLELEMENT (e, b.DEPTNO || ',') ORDER BY b.deptno, ename).EXTRACT ('//text()'),
 14  		    ',') as  depid
 15  	  FROM	 emp e,dept b
 16  	  WHERE  e.DEPTNO = b.DEPTNO
 17  	  group  by b.DEPTNO)
 18  SELECT SUBSTR
 19  	      (empid,
 20  	       INSTR (',' || empid, ',', 1, COLUMN_VALUE),
 21  	       INSTR (empid || ',', ',', 1, COLUMN_VALUE)
 22  	       - INSTR (',' || empid, ',', 1, COLUMN_VALUE)) empid,
 23  	    SUBSTR
 24  	      ("employee name",
 25  	       INSTR (',' || "employee name", ',', 1, COLUMN_VALUE),
 26  	       INSTR ("employee name" || ',', ',', 1, COLUMN_VALUE)
 27  	       - INSTR (',' || "employee name", ',', 1, COLUMN_VALUE)) "employee name",
 28  	    DECODE
 29  	      (COLUMN_VALUE,
 30  	       1,
 31  	       SUBSTR
 32  		 (depid,
 33  		  INSTR (' ' || depid, ',', 1, COLUMN_VALUE),
 34  		  INSTR (depid || ',', ',', 1, COLUMN_VALUE)
 35  		  - INSTR (',' || depid, ',', 1, COLUMN_VALUE)),
 36  	       NULL) depid
 37  FROM   data,
 38  	    TABLE
 39  	      (CAST
 40  		 (MULTISET
 41  		    (SELECT LEVEL
 42  		     FROM   DUAL
 43  		     CONNECT BY LEVEL <= LENGTH (empid)
 44  		       - LENGTH (REPLACE (empid, ',', '')) + 1)
 45  		  AS SYS.ODCIVARCHAR2LIST))
 46  /

EMPID                         employee name                        DEPID
----------------------------- ------------------------------------ -----------------
7782                          CLARK                                10
7839                          KING
7934                          MILLER
7876                          ADAMS                                20
7902                          FORD
7566                          JONES
7788                          SCOTT
7369                          SMITH
7499                          ALLEN                                30
7698                          BLAKE
7900                          JAMES
7654                          MARTIN
7844                          TURNER
7521                          WARD

14 rows selected.

Report message to a moderator

Re: How to view the rows in the table format using select query [message #555894 is a reply to message #555891]

Tue, 29 May 2012 13:30

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

Thanks Barbara!!

Report message to a moderator

Previous Topic:	rowmovement and fragmentation
Next Topic:	mutating table error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 31 16:13:11 CDT 2025