Converting Access PIVOT query to Oracle 11g

Question

I have an MS Access PIVOT query which needs to be migrated to Oracle 11g.

    TRANSFORM Format(Sum(tblOrderLine.ItemQtyCur), '#0.0') AS [The Value]

SELECT  tblOrderLine.OrderStatus, tblOrderLine.OrdNo, tblOrderLine.OrdlNo, Format(Sum(tblOrderLine.ItemQtyCur), '#0.0')  As SumConfQty

FROM tblOrder INNER JOIN (tblOrderLine LEFT JOIN tblArticle ON tblOrderLine.ItemId = tblArticle.ArtNo) ON tblOrder.OrdNo = tblOrderLine.OrdNo

GROUP BY  tblOrderLine.OrderStatus, tblOrderLine.OrdNo, tblOrderLine.OrdlNo

PIVOT tblOrderLine.LastDate

I am unable to convert this to oracle 11g PIVOT.

Can any one help me with this....

Edit :

Will it b like this.....

WITH pivot_data AS (
SELECT  tblOrderLine.OrderStatus, tblOrderLine.OrdNo, tblOrderLine.OrdlNo, Format(tblOrderLine.ItemQtyCur, '#0.0')  As SumConfQty
FROM tblOrder INNER JOIN (tblOrderLine LEFT JOIN tblArticle ON tblOrderLine.ItemId = tblArticle.ArtNo) ON tblOrder.OrdNo = tblOrderLine.OrdNo
GROUP BY  tblOrderLine.OrderStatus, tblOrderLine.OrdNo, tblOrderLine.OrdlNo
)
SELECT * FROM pivot_data
PIVOT XML (
Format(Sum(tblOrderLine.ItemQtyCur), '#0.0')
FOR tblOrderLine.LastDate
IN (
SELECT  tblOrderLine.LastDate
FROM tblOrder INNER JOIN (tblOrderLine LEFT JOIN tblArticle ON tblOrderLine.ItemId = tblArticle.ArtNo) ON tblOrder.OrdNo = tblOrderLine.OrdNo
GROUP BY  tblOrderLine.OrderStatus, tblOrderLine.OrdNo, tblOrderLine.OrdlNo
)
);

Nothing yet, dont know how to convert it – niklodeon Oct 11 '12 at 17:02 — niklodeon, Oct 11 '12 at 17:02

Taryn · Answer 1 · 2012-10-11T17:16:14.990

Your PIVOT syntax should be something like this:

select *
from
(
    SELECT tblOrderLine.OrderStatus, 
        tblOrderLine.OrdNo, 
        tblOrderLine.OrdlNo, 
        tblOrderLine.ItemQtyCur,
        tblOrderLine.LastDate
    FROM tblOrder 
    INNER JOIN tblOrderLine 
        ON tblOrder.OrdNo = tblOrderLine.OrdNo
    LEFT JOIN tblArticle 
        ON tblOrderLine.ItemId = tblArticle.ArtNo 
) x
pivot
(
    sum(ItemQtyCur)
    for LastDate in (yourDatesHere)
) p

If you want to use PIVOT XML, then the syntax would be similar to this:

select *
from
(
    SELECT tblOrderLine.OrderStatus, 
        tblOrderLine.OrdNo, 
        tblOrderLine.OrdlNo, 
        tblOrderLine.ItemQtyCur,
        tblOrderLine.LastDate
    FROM tblOrder 
    INNER JOIN tblOrderLine 
        ON tblOrder.OrdNo = tblOrderLine.OrdNo
    LEFT JOIN tblArticle 
        ON tblOrderLine.ItemId = tblArticle.ArtNo 
) x
pivot XML
(
    sum(ItemQtyCur) as SumItemQtyCur
    for (LastDate) in (SELECT DISTINCT LastDate FROM tblOrderLine)
) p

Here is a reference article about Oracle Pivots:

pivot and unpivot queries in 11g

@NikhilGupta can you create a [SQL Fiddle](http://sqlfiddle.com/#!4/d41d8) with some sample data for each table? — Taryn, Oct 11 '12 at 23:17

Converting Access PIVOT query to Oracle 11g

1 Answers1