CODEWITHSUNDEEP
oracle-databaseoracle11gpivotin-clause
Badal
Badal

Reputation: 295

Mapping multiple values of pivot in clause to one column in oracle 11g

I have this pivot query which is not working.

select * from mytable 
pivot ( 
count(batch_code) as Count
for batch_code
in ( (1,17,62) as grp1, (2,5,52) as grp2 )
)

It throws an ORA-00907:missing right parenthesis.

The syntax diagram for the pivot-in clause is here. Pivot in clause

As per it, I should be able to do (expr,expr) as alias.

Where am I going wrong? I am trying to map multiple values to a single column.

Thanks for your help.

Upvotes: 2

Views: 2163

Answers (1)

krokodilko
krokodilko

Reputation: 36107

It's unclear what are you trying to do with this (wrong) query.
The syntax using lists of values in brackets is used to pivot on more than one column.
The following query is an example how to use this syntax:

SELECT *
FROM x
PIVOT (
  SUM( value )
  FOR ( month, year ) IN (
     (1,2014) As a2014_1,
     (2,2014) As a2014_2,
     (3,2014) As a2014_3
 )
);

Demo: http://sqlfiddle.com/#!4/f4d18/1

| PRODUCT | A2014_1 | A2014_2 | A2014_3 |
|---------|---------|---------|---------|
|       1 |       5 |       7 |       2 |
|       0 |       3 |       7 |       5 |



===== EDIT =====

Unfortunately PIVOT clause cannot be used to do this task.
But You can use a basic pivot query like this:

SELECT column1,
       column2,
       column3,
       SUM( case when batch_code in (1,17,62)
            then 1 else 0 end )
            As grp1,
       SUM( case when batch_code in (2,5,52)
            then 1 else 0 end )
            As grp2
FROM mytable
GROUP BY column1,
         column2,
         column3

demo: http://sqlfiddle.com/#!4/9010a/2

Upvotes: 2

Related Questions