How to use pivot to get the row as column

A select statement we cannot run doesn't help much considering we know nothing about the system or data.

Can you provide some sample data and expected results?

You also do not indicate which column or columns you want to pivot. So, in addition to "sample data" for each table mentioned please also provide the "expected result" so we can identify what to pivot.

also: It isn't common convention in Oracle to use case sensitive column names. Why are all your column references within double quotes?
 (aside from the one column has a space in the name)

SELECT
      C.value        VENDORNAME
    , B.documentguid PROCESSEDDOC
    , B.touch        TOUCHED
FROM historyoffield C
   , (
           SELECT
                 A.documentguid
               , SUM (CASE WHEN A."value" <> A."value previous" THEN '1' END) TOUCH
           FROM (
                 SELECT
                       documentguid
                     , fieldname
                     , value
                     , "value previous"
                 FROM historyoffield
                 WHERE fieldname IN ('InvoiceNumber', 'InvoiceDate', 'Total', 'VendorID')
                 AND receivedtime IN (
                             SELECT
                                   MAX(receivedtime)
                             FROM historyoffield
                             WHERE documentguid IN (
                                   SELECT
                                         documentguid
                                   FROM lastversionofdocument
                                   WHERE exported = 1
                                   AND receivedtime >= '08/03/2017 12:00:00 AM'
                                    )
                             GROUP BY
                                   documentguid
                              )
                  ) A
           GROUP BY
                 A.documentguid
     ) B
WHERE C.documentguid = B.documentguid
AND C.fieldname IN ('APInvoiceType', 'InvoiceNumber', 'InvoiceDate','VendorID','Total')
GROUP BY
      C.value
    , B.documentguid
    , B.touch

Select allOpen in new window

also, if you are using a inner join between C and B (WHERE C.documentguid = B.documentguid) then I don't understand how you will get results for 'APInvoiceType'

line 17  WHERE fieldname IN ('InvoiceNumber', 'InvoiceDate', 'Total', 'VendorID')
line 37:  AND C.fieldname IN ('APInvoiceType', 'InvoiceNumber', 'InvoiceDate','VendorID','Total')

Hi,

Thank you for response.

We have the three tables.
1) HistroyOfBatch
Having the Batch Information
2)HistoryOfDocuments
Having the docuement Information
3)HistoryOfFields
Having the Field Information.
-- In the Field table Data is storing this way

Field Name                Value               OldValue
1)Invoice Number     666                      333
2)Invoice Date              12/12/2016    11/11/2016
3)Total Amount            123                  1222

we need the output like this
Invoice Number       Invoice Date       TotalAmount     documentid
666                                 12/12/2016       123                   xyz

BatchID and DocumentID is are in all three tables , we can use for this joining the tables...

Please help on it.
Let me know if u need any information


Thanks

In the query you have provided HistroyOfBatch and HistoryOfDocuments are not mentioned and neither seem relevant to the pivot result either.

So ignoring those 2 tables: Does this work?

SELECT
      h.documentguid
    , max(case when h.fieldname = 'InvoiceNumber' then h.value end) as InvoiceNumber
    , max(case when h.fieldname = 'InvoiceDate' then h.value end) as InvoiceDate
    , max(case when h.fieldname = 'VendorID' then h.value end) as VendorID
    , max(case when h.fieldname = 'Total' then h.value end) as Total
FROM historyoffield h
inner join (
      SELECT
            documentguid
            , MAX(receivedtime) as receivedtime
      FROM historyoffield
      WHERE documentguid IN (
            SELECT
                  documentguid
            FROM lastversionofdocument
            WHERE exported = 1
            AND receivedtime >= to_date('08/03/2017','dd/mm/yyyy')
            )
      GROUP BY
            documentguid
      ) dt ON h.documentguid = dt.documentguid and h.receivedtime = dt.receivedtime
WHERE h.fieldname IN ('InvoiceNumber', 'InvoiceDate', 'Total', 'VendorID')
GROUP BY
      h.documentguid

Select allOpen in new window

I previously asked:
a. why are you using double quotes for all columns?
b. how will you locate documents with fieldname = 'APInvoiceType' ? (it does not seem possible)

and now I add this:
What is lastversionofdocument? (a view? a table?)

YES,
lastversionofdocument - TABLE (IT IS STORING THE LAST RECORD OF HISTORYOFDOCUMENT TABLE RECORD )

Regards,
Praveen

and my other questions?

a. why are you using double quotes
 for all columns?  - if i given without quotes -- getting error.
b. how will you locate documents

 with fieldname = 'APInvoiceType' ? (it does not seem possible)  -- Yes it's not working ...

This is the original query...
SELECT "C"."Value" "VENDORNAME",
COUNT("B"."DocumentGuid") "PROCESSED",
COUNT( "B"."TOUCH") "TOUCHED"
FROM "HistoryOfField" C,
  (SELECT "A"."DocumentGuid",
    SUM(
    CASE
      WHEN "A"."Value" <> "A"."Value Previous"
      THEN '1'
    END) "TOUCH"
  FROM
    (SELECT "DocumentGuid",
      "FieldName",
      "Value",
      "Value Previous"
    FROM "HistoryOfField"
    WHERE "FieldName"  IN ('InvoiceNumber','InvoiceDate','Total','VendorID')
    AND "ReceivedTime" IN
      (SELECT MAX("ReceivedTime")
      FROM "HistoryOfField"
      WHERE "DocumentGuid" IN
        (SELECT "DocumentGuid"
        FROM "LastVersionOfDocument"
        WHERE "Exported"    =1
        AND "ReceivedTime" >= '08/03/2017 12:00:00 AM'
        )GROUP BY "DocumentGuid"
      )
    ) A
  GROUP BY "A"."DocumentGuid"
  ) B
WHERE "C"."DocumentGuid" = "B"."DocumentGuid"
AND "C"."FieldName"     IN ('VendorName')
GROUP BY "C"."Value"

Does this query work without producing an error message?
Are the results correct?

SELECT
      h.documentguid
    , max(case when h.fieldname = 'InvoiceNumber' then h.value end) as InvoiceNumber
    , max(case when h.fieldname = 'InvoiceDate' then h.value end) as InvoiceDate
    , max(case when h.fieldname = 'VendorID' then h.value end) as VendorID
    , max(case when h.fieldname = 'Total' then h.value end) as Total
FROM historyoffield h
inner join (
      SELECT
            documentguid
            , MAX(receivedtime) as receivedtime
      FROM historyoffield
      WHERE documentguid IN (
            SELECT
                  documentguid
            FROM lastversionofdocument
            WHERE exported = 1
            AND receivedtime >= to_date('08/03/2017','dd/mm/yyyy')
            )
      GROUP BY
            documentguid
      ) dt ON h.documentguid = dt.documentguid and h.receivedtime = dt.receivedtime
WHERE h.fieldname IN ('InvoiceNumber', 'InvoiceDate', 'Total', 'VendorID')
GROUP BY
      h.documentguid

Select allOpen in new window