Need Help On write Oracle SQL

Hi Experts,
I need a help on build a report by using the SQL.
I have a two tables
1) Document table
2) Fields table

The relation between these tables are
documentid in both the tables..

Basically I need the SQL to get the report as shown in the spread sheet. ( Attached excel sheet)

Thank you
Who is Participating?
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.

Bill PrewIT / Software Engineering ConsultantCommented:
Please share what columns exist in which table...

slightwv (䄆 Netminder) Commented:
I agree that it is impossible to answer without more information.

Here is a guess:
	Invoice_Name_Confidence+Invoice_date_Confidence as Total
	(Invoice_Name_Confidence+Invoice_date_Confidence)/2 myAVG
from documents d join fields f on d.documentid=f.documentid

Open in new window

I don't think my Total column is correct given your numbers but you get the idea.  I also don't understand the Total-Confidence column.  My AVG example is missing the Total Column because of the above statement that I don't think my guess is correct.

Anyway, it's an example loosely based on your data.
PRAVEEN TAuthor Commented:

Thank you for the reply.

I have attached the excel sheet.
Document - table have a one column - 1) documentID
Field - Table have following columns

Please let me know if any questions ..

Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

slightwv (䄆 Netminder) Commented:
>>Please let me know if any questions ..

Please provide sample raw data from the two tables and expected results from your sample data.

Are you saying you have a field column containing a string like 'invoice_name' and it's value is 3333, a string 'vendor_name' with value 'Xyz Tech'?

If so, yuck.  Not really a Relational Design, is it.  Not sure I would pay for Oracle RDBMS and not leverage the 'R'elational part of it.
slightwv (䄆 Netminder) Commented:
I'll let you do the confidence and AVG math.  I gave you the basics in the post above.

Here is an example of turning key-value pairs into columns.

If you can change the design, I would strongly encourage you to do so!

create table documents(
	DocumentID number

insert into documents values(1);
insert into documents values(2);
insert into documents values(3);

create table fields(
	DocumentID number,
	Field varchar2(10),
	Value  varchar2(10),
	Confidence number

insert into fields values(1,'Vendor','Bob',10);
insert into fields values(2,'Vendor','Fred',10);
insert into fields values(1,'Invoice','3333',10);

select  d.documentid, vendor_name, invoice_name from (
	select documentid,
		max(case when field='Vendor' then value end) vendor_name,
		max(case when field='Invoice' then value end) invoice_name
	from fields
	group by documentid
) f
join documents d on f.documentid=d.documentid

Open in new window

PRAVEEN TAuthor Commented:
Thank you for the reply.

I have given the sample data only.

Are you saying you have a field column containing a string like 'invoice_name' and it's value is 3333, a string 'vendor_name' with value 'Xyz Tech'?
Sorry.. it is invoice_number
'vendor_name' with value 'Xyz Tech' - it's sample value only..

Please let me know if you have any questions..

slightwv (䄆 Netminder) Commented:
>>I have given the sample data only.

I understand that but again, what information you have provided isn't enough to help us understand the problem.

If the test case I posted above isn't right, please change it or add to it.  Then post the expected results based on the actual test case.
PRAVEEN TAuthor Commented:
Can we use the pivot to convert the row to column?

slightwv (䄆 Netminder) Commented:
Pretty sure you cannot use pivot with unknown key/pair values.

Now if you hard-code the values to pivot on then sure but that defaults the perceived flexibility you have with unknown key-pair values in your design.  Although off the top of my head, I'm not sure how.  You need to aggregate columns to use pivot.
slightwv (䄆 Netminder) Commented:
It wasn't that bad.  You still need to hard-code the values but here is the pivot example using the test case I posted above:
select * from (
	select documentid, field, value from fields
pivot (max(value) for field in ('Vendor' vendor_name, 'Invoice' invoice_name))

Open in new window

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

From novice to tech pro — start learning today.