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.
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.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
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
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;
>>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;
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
If you want to give a few points for information gathering, I'm sure that will be fine as well.
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:
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
/
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
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
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?