CODEWITHSUNDEEP
oracle-databaseoracle10gpivot
Björn T.J.M. Spruit
Björn T.J.M. Spruit

Reputation: 13

Oracle pivot query results in a "command not properly ended" error

When firing the following query to a 10g Oracle database:

    SELECT * FROM (
        SELECT T1.ID, T2.ACCT_NO, ROW_NUMBER() OVER (PARTITION BY T1.ID ORDER BY T1.ID DESC) AS RRRRRRR 
        FROM TABLE1 T1
        INNER JOIN TABLE T2 ON T1.ID = T2.ID
        WHERE T1.ID = 666
    )
    PIVOT (MIN(T1.ID) AS ALIAS1 FOR RRRRRRR IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10));

I get an "command not properly ended" error. I've searched for Oracle pivot examples and they all pretty much showed the same example. What am I missing here?

Upvotes: 1

Views: 2379

Answers (2)

Taryn
Taryn

Reputation: 247810

As @APC pointed out there is no PIVOT function in oracle 10g, so you can use an aggregate function and a CASE, similar to this:

SELECT id, acct_no, 
  min(case when RRRRRRR = 1 then id end) as '1',
  min(case when RRRRRRR = 2 then id end) as '2',
  min(case when RRRRRRR = 3 then id end) as '3',
  min(case when RRRRRRR = 4 then id end) as '4',
  min(case when RRRRRRR = 5 then id end) as '5',
  min(case when RRRRRRR = 6 then id end) as '6',
  min(case when RRRRRRR = 7 then id end) as '7',
  min(case when RRRRRRR = 8 then id end) as '8',
  min(case when RRRRRRR = 9 then id end) as '9',
  min(case when RRRRRRR = 10 then id end) as '10'
FROM 
(
  SELECT T1.ID, T2.ACCT_NO, ROW_NUMBER() OVER (PARTITION BY T1.ID ORDER BY T1.ID DESC) AS RRRRRRR 
  FROM TABLE1 T1
  INNER JOIN TABLE T2 ON T1.ID = T2.ID
  WHERE T1.ID = 666
) x
GROUP BY id, acct_no

Upvotes: 2

APC
APC

Reputation: 146249

Your syntax is impeccable. Unfortunately PIVOT was introduced in Oracle 11g and you're using 10g.

Upvotes: 1

Related Questions