CODEWITHSUNDEEP
sqloracle-database
KarmaOfJesus
KarmaOfJesus

Reputation: 101

Remove quotes from column name

Assume i have following table:

create table t1(
    clid number,
    category varchar2(100)
);

insert into t1 values(1, 'A');
insert into t1 values(1, 'B');
insert into t1 values(2, 'A');
insert into t1 values(2, 'C');
insert into t1 values(3, 'A');
insert into t1 values(3, 'B');

I need to pivot this table and count the number of times per each each CLID value. So in order to achieve this I use following code:

select * from t1
pivot(
    count(item)
    for item in('A', 'B', 'C')
)

And get the result:

CLID  'A' 'B' 'C'
1     1   1   0
2     1   0   0
3     1   1   0

Everything is great except quotes in names of new columns. I've tried the next code:

alter table t1 rename column 'A' to A

And it raises following error:

Error starting at line : 537 in command -
alter table t1 rename column 'A'
Error report -
ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

So my question is how can I rename column name which is in quotes (and in this way get rid of quotes)?

Appreciate your help.

Upvotes: 1

Views: 1638

Answers (2)

Littlefoot
Littlefoot

Reputation: 142705

Column name seems to be category, not item.

So:

SQL> select * from t1
  2  pivot(
  3      count(category)
  4      for category in('A' a, 'B' b, 'C' c)            --> this
  5  )
  6  /

      CLID          A          B          C
---------- ---------- ---------- ----------
         1          1          1          0
         2          1          0          1
         3          1          1          0

SQL>

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269703

How about just using conditional aggregation?

select sum(case when item = 'A' then 1 else 0 end) as A,
       sum(case when item = 'B' then 1 else 0 end) as B,
       sum(case when item = 'C' then 1 else 0 end) as C       
from t1;

Upvotes: 1

Related Questions