3 Replies Latest reply: Oct 15, 2013 5:50 AM by Hoek RSS

    Question on PIVOT() function

    user5530072

      Hello everyone,

        I have the below dataset and I would like to display it in the format that I've highlighted below:Please suggest.

       

       

      ELEMENT_NAMEVALUE
      PO Number4500018345
      Invoice Number77777
      Invoice Amount95000
      Supplier NameABC
      PO Line Number80
      Invoice Date2013-06-10
      PO Line Number70
      Invoice Amount5000
      Supplier NameCDF

       

      Required format

       

      Invoice NumberPO NumberInvoice AmountSupplier NameInvoice DatePO Line Number
      77777450001834595000ABC2013-06-1070
      7777745000183455000CDF2013-06-1080
        • 1. Re: Question on PIVOT() function
          Marwim

          Hello,

           

          this is the forum space for the tool

          SQL Developer (Not for general SQL/PLSQL questions)

           

          Please mark your question as answered and post again in SQL and PL/SQL

           

          Regards

          Marcus

          • 2. Re: Question on PIVOT() function
            Ramin Hashimzadeh

            Hi, see below link

            Pivot the table

            Reg: col to rows-

             

            ----

            Ramin Hashimzade

            • 3. Re: Question on PIVOT() function
              Hoek

              Yet another reason to avoid the EAV model.

              Anyway, you could try something along these lines:

              SQL> -- generating sample data:

              SQL> with t as (

                2  select 'PO Number' element_name,  '4500018345' value from dual union

                3  select 'Invoice Number', '77777' from dual union

                4  select 'Invoice Amount', '95000' from dual union 

                5  select 'Supplier Name', 'ABC' from dual union 

                6  select 'PO Line Number', '80' from dual union 

                7  select 'Invoice Date', '2013-06-10' from dual union 

                8  select 'PO Line Number', '70' from dual union 

                9  select 'Invoice Amount', '5000' from dual union 

              10  select 'Supplier Name', 'CDF' from dual

              11  )

              12  --

              13  -- actual query:

              14  --

              15  select nvl(invno, lag(invno) over (order by invno)) "Invoice Number"

              16  ,      nvl(pono, lag(pono) over (order by pono)) "PO Number"

              17  ,      nvl(invam, lag(invam) over (order by invam)) "Invoice Amount"

              18  ,      nvl(supl, lag(supl) over (order by supl)) "Supplier Name"

              19  ,      nvl(invdt, lag(invdt) over (order by invdt)) "Invoice Date"

              20  ,      nvl(poline, lag(poline) over (order by poline)) "PO Line Number"

              21  from ( select min(case when element_name = 'Invoice Number' then value end ) invno

              22         ,      min(case when element_name = 'PO Number' then value end ) pono

              23         ,      min(case when element_name = 'Invoice Amount' then value end ) invam

              24         ,      min(case when element_name = 'Supplier Name' then value end ) supl

              25         ,      min(case when element_name = 'Invoice Date' then value end ) invdt

              26         ,      min(case when element_name = 'PO Line Number' then value end ) poline

              27         from ( select element_name

              28                ,      value

              29                ,      row_number() over (partition by element_name order by value) rn    

              30                from   t

              31              )

              32         group by rn

              33       );

               

              Invoice Nu PO Number  Invoice Am Supplier N Invoice Da PO Line Nu

              ---------- ---------- ---------- ---------- ---------- ----------

              77777      4500018345 5000       ABC        2013-06-10 70

              77777      4500018345 95000      CDF        2013-06-10 80

               

              2 rows selected.