Transposing data 3

[LaurenM]

Hi everyone, I'm trying to get some data displayed, but I want it transposed, and I can't figure it out, and the examples that I have found on the 'net haven't addressed my issue. What I have is a list of contacts and what company they are with, so a straight up select query will give me that data. But, here's the difficult part, several contacts are the the contact for more than one company, so what I am trying to do is list each contact in the first column, and in the following columns list the companies that they are contacts for. I would like it to display something like this:

Col 1 Col 2 Col 3 Col 4
Jane Doe Company XYZ
Joe Smith Company A Company B Company C
Micheal Smith Company Q
Neal O'Church Company W Company R

If you have anything that would help, let me know.
Thanks

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[BJCooperIT]

What you want is a pivot query. There are several ways to do that, but here is one: Pivot Query.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

Lauren,

There are multiple ways to solve this issue. The solutions differ depending upon the output format.

Method 1: [ul][li]Produces your output format, above.[/li][li]Requires your placing some limit on the number of companies that a contact can represent.[/li][li]Involves more complex (but SQL-only) code.[/li][li]Performance is poor compared to Method 2, below.[/li]

(Because the code for Method 1 is so much more complicated, I'll create/post sample code only if Method 2, below, is absolutely unacceptable for some reason.)[/ul]

Method 2: [ul][li]Requires a user-defined (PL/SQL) function and SQL.[/li][li]Is unlimited in the number of companies a contact can represent[/li][li]Performs faster than Method 1, above.[/li]

(Confirmation of data)
select * from company_contact;

COMPANY_NAME CONTACT_NAME
------------ -------------
Company XYZ  Jane Doe
Company A    Joe Smith
Company B    Joe Smith
Company C    Joe Smith
Company Q    Michael Smith
Company W    Neal O'Church
Company R    Neal O'Church

(User-defined function)
create or replace function get_contact_companies (contact_in varchar2) return varchar2 is
    hold_companies varchar2(4000);
begin
    for x in (select company_name
                from company_contact
               where contact_name = contact_in
               order by contact_name) loop
        hold_companies := hold_companies||', '||x.company_name;
    end loop;
    return ltrim(hold_companies,', ');
end;
/

Function created.

(Actual SQL query, using user-defined function)
select distinct contact_name, get_contact_companies(contact_name) Companies
  from company_contact;

CONTACT_NAME    COMPANIES
--------------- -------------------------------
Jane Doe        Company XYZ
Joe Smith       Company A, Company B, Company C
Michael Smith   Company Q
Neal O'Church   Company W, Company R

4 rows selected.

[/ul]Let us know if Method 2 is acceptable.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[LaurenM]

Thanks BJ & Santa, I was hoping that I could get away without creating a function, but it looks like this will work.


Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[taupirho]

You can do this without creating a function. Analytics to the rescue once again.

Take for example the scott.emp table. We have

1* select deptno,ename from scott.emp
SQL> /

DEPTNO ENAME
---------- ----------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS

DEPTNO ENAME
---------- ----------
30 JAMES
20 FORD
10 MILLER

14 rows selected.


So for any one department we have multiple employees

Run this query and we get

1 select deptno,substr(max(sys_connect_by_path(ename,',')),2) names
2 from
3 (select deptno,ename,row_number() over (partition by deptno order by ename)
rn
4 from scott.emp
5 )
6 start with rn=1
7 connect by prior rn = rn -1
8 and prior deptno = deptno
9 group by deptno
10* order by deptno
SQL> /

DEPTNO
----------
NAMES
--------------------------------------------------------------------------------
10
CLARK,KING,MILLER

20
ADAMS,FORD,JONES,SCOTT,SMITH

30
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD






In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

Very clever, Taupirho...Certainly deserving of a

!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[karluk]

Taupirho, would you be willing to put your solution into a FAQ? Questions about pivot queries come up all the time, and your approach is the best I've seen.

 

[taupirho]

Sure, I will post a FAQ when time allows.

I have to say I didn't invent this technique. Came across it on the web, most probably on somewhere like asktom although I can't remember exactly where. It will only work with V9 upwards since this is when the sys_connect_by_path came into being I believe.


In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

I just got around to actually trying Taupirho's excellent code...It won't work on my 9iR2 (9.2.0.1)...it works on my 10g, however.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[taupirho]

I'm running

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production




In order to understand recursion, you must first understand recursion.

 

[Thargy]

Santa,

I believe that Analytics are only available in Enterprise edition, regardless of whether it's 9i or 10g.

I felt obliged to mention that in 11g Oracle has added both PIVOT and UNPIVOT clauses to SQL, so eventually this will cease to be an issue.

Regards

T

 

[SantaMufasa]

Tharg,

As an update, my failed test ran on 9i Enterprise...my successful test ran on 10g Standard Edition.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[Thargy]

Thanks Santa - I sit corrected.

Regards

T