Oracle Sql code to achieve cross tab / pivot

Scenario: I have 2 tables, orders and consultants as follows

Orders                                        
orderID
1001
1002
1003
etc

Consultants
person        role
person1      sales
person2      Account manager
person3      sales
person4      Account manager
person5      sales
person6      Account manager
etc


These tables are linked, the issue is when running the query i get the following

orderID             person      
1001                  person1
1001                  person2      
1002                  person3
1002                  person4
1003                  person5
1003                  person6

What I need to achieve is:

orderID          Sales                 Account manager
1001               Person1            Person2
1002               person3            person4
1003               person5            person6


Just to be clear, the number of roles could increase, therefore, the query would need to be dynamic


Any pointers or help would be greatly appreciated

Many thanks in advance,

Keith.
kaleesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You cannot have a dynamic number of columns at run time.

You can create a CSV or other delimited list of values with an unbounded number of items or you need to pick a maximum number.

While you decide which method you want, what is your Oracle version?
kaleesAuthor Commented:
Many thanks for your prompt reply, Max number of roles would be 4, I guess that I would be able to accommodate more roles in the future by amending the code?

I am unsure as to our current version as this is a hosted database. I know that I was advised to install version 11.2.0 ODBC client if this is of any help?
slightwv (䄆 Netminder) Commented:
Client version doesn't help although it does bring up a issue:
Since you mention ODBC, what tool/product will you be executing this with?

Some tools don't like some Oracle Syntax.

The version is important to know if you can use newer SQL like PIVOT or if you need to use the older SQL tricks to pull it off.

>> guess that I would be able to accommodate more roles in the future by amending the code?

You can add as many columns as you want/need.  You just need to know the number before the SQL is parsed.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
I would also love to set up a working test case but cannot.

The table data you provided doesn't show how the tables are related.  We will need to know this before we can provide working SQL.
kaleesAuthor Commented:
I am using Toad 12.7.0.121

Bit of a bad example as I have tried to simulate the scenario due to this being a commercial database and conscious of data!

I have attached a number of .csv files which are cut down versions of the actual files.

consultants.csv
contracts.csv
incorrect.csv - the result I get
what is required


The real scenario is that the Consultants and Contracts are linked by Account_Code

I am  struggling to get an Oracle version, I might have to wait until tomorrow

Regards,

Keith
Consultants.csv
Contracts.csv
Incorrect.csv
What-is-required.csv
slightwv (䄆 Netminder) Commented:
Thanks for the data.  I'll try to get a little time later to see what I can come up with.

>>I am using Toad 12.7.0.121

Toad needs ODBC drivers?  Not a Toad user but that surprises me.  I'm not sure ODBC supports all of Oracle's SQL options.

>>I am  struggling to get an Oracle version

If you can connect to the database try:
select * from v$version;
kaleesAuthor Commented:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production                      
PL/SQL Release 11.2.0.4.0 - Production                                          
CORE      11.2.0.4.0      Production                                                      
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production                        
NLSRTL Version 11.2.0.4.0 - Production                  

Not sure that Toad needs ODBC, however, that is what we are using at the moment.

As may be obvious, I am very new to Oracle!

Many thanks for your continued support,

Keith.
PortletPaulEE Topic AdvisorCommented:
If the data always 1 person, per role, per contract, like your expected result:
| CONTRACT_NUMBER | ACCOUNT_CODE | Client Support | SALESPERSON |
|-----------------|--------------|----------------|-------------|
|            1212 |         BEN1 |             KD |          RD |
|             711 |         GEZ1 |             KD |         PD1 |
|            1012 |         CON1 |             KD |          JB |

Open in new window

select
      t.contract_number
    , t.account_code
    , max(case when c.role = 'Client Support' then c.employee_code end) as "Client Support"
    , max(case when c.role = 'Salesperson' then c.employee_code end) as Salesperson
from contracts t
inner join consultants c on t.account_code = c.account_code
group by
      t.contract_number
    , t.account_code
order by
      t.contract_number
    , t.account_code
;

Open in new window

If there are more then one person in a role on a single contract then it won't work. For this, instead of MAX() we could use LISTAGG() perhaps.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kaleesAuthor Commented:
Paul,

Many thanks, that has worked a treat.

I need some guidance please, are you guys ok for me to accept both solutions and split as slightwv prompted me for all of the info and Paul provided a working solution.

Please let me know your thoughts,

Keith
slightwv (䄆 Netminder) Commented:
I'm OK with Paul getting the points.  He beat me to the SQL.

If you want to give a few points for information gathering, I'm sure that will be fine as well.
slightwv (䄆 Netminder) Commented:
Paul gave you the old way I mentioned.

Since I was pretty much done with my test setup when posted, I went ahead and used the new Oracle PIVOT command.

Try this as well:
select * from (
	select contract_number,
		ct.account_code,
		employee_code,
		role
	from myContracts ct join myConsultants cl on cl.account_code=ct.account_code
)
pivot 
(
   min(employee_code)
   for role in ('Salesperson' as "Sales Person", 'Client Support' as "Client Support" )
)
order by 1
/

Open in new window

PortletPaulEE Topic AdvisorCommented:
It is extremely rare for me to beat slightwv to any Oracle question, so any Oracle points are especially sweet :)

Absolutely no issue with the points split. I do wish more folks would acknowledge that asking for information is often a very necessary part of getting to the solution. I would suggest you always try to provide "sample data" and "expected result", mentioning your dbms version is also very helpful.

By the way, I still prefer to "olde fasjioned" case expression method of pivoting because it works in most (all?) Oracle versions and is transportable to other dbms platforms as an extra bonus.

Cheers, Paul
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.