SQL & PL/SQL

Hi,

As you found out, Pivot means produce separate columns from separate rows.
What you want is called String Aggregation .

[AskTom.oracle.com|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402] shows several different ways to do it.

Hi,

Oracle SQL does not have a direct mechanism for allowing multiple values from the same column to be displayed in the same row of output.

SQL is designed not to disallow repeating values on a single line of output, but Oracle has several tricks for allowing repeating groups of same-column values on a single line of output.

Here are several ways to pivot multiple rows into one row:

http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm

Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of "Oracle Tuning: The Definitive Reference"
http://www.rampant-books.com/t_oracle_tuning_book.htm
"Time flies like an arrow; Fruit flies like a banana".

Maybe not until 11gR2:

SQL>  SELECT deptno,
       listagg (ename, ',') WITHIN GROUP (ORDER BY ename)
        enames
FROM emp
GROUP BY deptno
/
    DEPTNO ENAMES                                            
---------- --------------------------------------------------
        10 CLARK,KING,MILLER                                 
        20 ADAMS,FORD,JONES,SCOTT,SMITH                      
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD              

3 rows selected.

;)

Coool...Gotta have one those.

(Preferably within the next five years)

:-)

i encountered this question during an interview for Oracle 9i. the link which you provided has not shown for single column . Though we can archive this by 11g , i wish to know how to do it in Oracle 9i and that too using by single SELECT query.. NOT by using PL/SQL block.

That should work:

SQL>  select deptno,
       rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
from emp
group by deptno
/
    DEPTNO ENAMES                                  
---------- ----------------------------------------
        10 CLARK,MILLER,KING                       
        20 SMITH,FORD,ADAMS,SCOTT,JONES            
        30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD    

3 rows selected.

Nice example.

A bit of a different approach using analytic functions:

CREATE TABLE T20(
  DEPT NUMBER(10),
  EMPNAME VARCHAR2(30));

INSERT INTO T20 VALUES (10,'THAMBI');
INSERT INTO T20 VALUES (10,'PETER');
INSERT INTO T20 VALUES (10,'ANTHONY');
INSERT INTO T20 VALUES (20,'GEORGE');
INSERT INTO T20 VALUES (20,'MICHAEL');

COMMIT;

COLUMN EMPNAME FORMAT A10

SELECT
  DEPT,
  EMPNAME,
  ROW_NUMBER() OVER (PARTITION BY DEPT ORDER BY EMPNAME DESC) RN
FROM
  T20;

 DEPT EMPNAME            RN
----- ---------- ----------
   10 THAMBI              1
   10 PETER               2
   10 ANTHONY             3
   20 MICHAEL             1
   20 GEORGE              2

Now that the employees are separated by department, SYS_CONNECT_BY_PATH is used to draw the related employees into a single row:

COLUMN EMP_LIST FORMAT A30

SELECT
  DEPT,
  MAX(SUBSTR(SYS_CONNECT_BY_PATH(EMPNAME,','),2)) EMP_LIST
FROM
  (SELECT
    DEPT,
    EMPNAME,
    ROW_NUMBER() OVER (PARTITION BY DEPT ORDER BY EMPNAME DESC) RN
  FROM
    T20)
CONNECT BY PRIOR 
  (DEPT||RN)=(DEPT||RN-1)
START WITH
  RN=1
GROUP BY
  DEPT;

 DEPT EMP_LIST
----- ------------------------------
   20 MICHAEL,GEORGE
   10 THAMBI,PETER,ANTHONY

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Hi,

But Oracle 8i is enough smart to make this job, look at this:

HR: XE > select * from phone_table;

I PHONE
- ----------
1 11134134
2 21132345
3 31134134
4 42345678
5 51001100
6 61231100
7 71345100

7 rows selected.

HR: XE > select a1.phone|| ', ' ||a2.phone|| ', ' ||a3.phone|| ', ' ||a4.phone|| ', ' ||a5.phone|| '
, ' ||a6.phone|| ', ' ||a7.phone all_phones
  2      from
  3  (select x.id, x.phone from phone_table x) a1,
  4  (select x.id, x.phone from phone_table x) a2,
  5  (select x.id, x.phone from phone_table x) a3,
  6  (select x.id, x.phone from phone_table x) a4,
  7  (select x.id, x.phone from phone_table x) a5,
  8  (select x.id, x.phone from phone_table x) a6,
  9  (select x.id, x.phone from phone_table x) a7
 10      where
 11  a1.id >a2.id and
 12  a2.id >a3.id and
 13  a3.id >a4.id and
 14  a4.id >a5.id and
 15  a5.id >a6.id and
 16  a6.id >a7.id ;

ALL_PHONES
--------------------------------------------------------------------------------
71345100, 61231100, 51001100, 42345678, 31134134, 21132345, 11134134

HR: XE > 

The idea is to use a key and a little imagination.

Regards,
Ion

Incorrect. A column can be a collection that holds multiple values (in a structured way too, and not some restrictive string aggregation technique).

SQL> -- define the collection data type 
SQL> create or replace type TStrings is table of varchar2(4000);
  2  /

Type created.

SQL>
SQL> -- use MULTISET() to construct that collection dynamically in a SQL projection
SQL> select
  2          d.dname,
  3          cast( multiSet( select e.ename from emp e where e.deptno = d.deptno order by 1) as TStrings ) as EMPLOYEE_LIST
  4  from       dept d
  5  order by d.dname
  6  /

DNAME                EMPLOYEE_LIST
-------------------- --------------------------------------------------------------------------------
ACCOUNTING           TSTRINGS('CLARK', 'KING', 'MILLER')
OPERATIONS           TSTRINGS()
RESEARCH             TSTRINGS('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')
SALES                TSTRINGS('ALLEN', 'BLAKE', 'JAMES', 'MARTIN', 'TURNER', 'WARD')

SQL>

The above works on 10g. May also have worked in on 9i.

Thanks for all the reples. Yes I tried with XML functions.. as below..

select
rtrim (xmlagg (xmlelement (e, emp_name || ',')).extract ('//text()'), ',') Empnames
from emp

Its working. As I already said that I have only one column in the table, have no department column. If i have deaprtment column, other solultions are working fine. Thanks a lot.

Regards,
Thambi

None of the solutions shown requires a department column specifically. My example uses it.. as an example. The MULTISET function is a standard SQL function. And how you choose to use it does not depend on having a specific column at all!.

You want a single row with all the employees listed? Then the following can be used:

SQL> select cast( multiSet( select e.ename from emp e ) as TStrings ) as LIST from dual;

LIST
------------------------------------------------------------------------------------------------------------------------------------------------------------
TSTRINGS('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')

The question that you need to ask yourself is why use such a technique? For rendering the data a specific way in the client? Well, rendering data is NOT a SQL function and in essence a result of ignorance of how to correctly use client-server. Rendering on the client is dealt with by the client itself. Using SQL to do it.. not only nasty (as many of these examples above are), but also far from optimal and efficient SQL. And in most cases, will not scale. Increase the data volume of the table queried and there will be a hefty performance knock as SQL is incorrectly used.

Hi ,

yes your solution will work. since i don't have privilege to create collection data type in my test database, I can't test it. Logically, your solution should be working. and also you have shown the example. I was asked this question by an interviewer, that's why i posted in forum. Thanks for your help.


Regards,
Thamib

You should request the access piv for creating types. There is really no "+harm+" or security issues in allowing this right. If you can create tables, you should be able to create types too.
Silly question IMO. Reason why I say it is that the actual problem is not presented. The interviewer thinks he/she knows the ideal solution to a problem. And then want you to tell them how to implement that solution. Sorry, this is not how development works. They want someone to simply generate code for them? They should get a case tool, code monkey or something.

Developers are employed to do two basic things:
- solve technical problems
- write technical solutions

Just throwing the 2nd part at the developer, writing the technical solution, without allowing the developer to participate in the solving part is a major mistake.

Billy --

I would like to stem a little discussion from this point.

Where do you draw the line between the correct use of SQL or not? Or between rendering on the client and just returning data?

Now with LISTAGG, PIVOT and UNPIVOT all available to us would these be considered correct uses of SQL?

Where does this leave the TO_CHAR function? Is this considered rendering?

I'm fully expecting a fuzzy answer with something along the lines of "do the work where it makes the most sense" from a ease of development and maintainability perspective but I just wanted to ask.

Thanks! :)

Hi Centinul

As the watches feature of OTN's Jives forums is still broken, I find it very difficult to keep tabs on updates to old threads. More than often, I will miss an update to a watched thread that is older than 3 or 4 days.

So sorry - I missed this response from you entirely until you started a new thread that referred to this one.

I've raised the watches feature twice in the Commnity Forum in the past, but it seems there is some kind of performance penalty knock that results and that we will not get this old feature back. :-(