0

I have an access to Oracle server. There is a table on the Oracle server called Transactions which contains the following data:

enter image description here

I don't known the number of values, so we need to implement dynamic sql in Oracle. I need to pivot that data so the results are:

enter image description here

Any suggestions?

Community
  • 1
  • 1
Taie
  • 1,021
  • 16
  • 29
  • [Dynamic pivot in oracle sql](https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql), [Oracle Dynamic Pivoting](https://stackoverflow.com/questions/50259728/oracle-dynamic-pivoting) – Ponder Stibbons Mar 11 '20 at 10:51
  • Don't do it in SQL, do it inside your application –  Mar 11 '20 at 11:05

1 Answers1

1

You can use conditional aggregation:

select subno,
       sum(case when offer = 'offer1' then 1 else 0 end) as offer1,
       sum(case when offer = 'offer2' then 1 else 0 end) as offer2,
       sum(case when offer = 'offer3' then 1 else 0 end) as offer3
from t
group by subno;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • actually, i have more than 1000 offers. so, i would need to add that number of rows for the different cases. Can we handle this? – Taie Mar 11 '20 at 10:57
  • @Taie . . . Yes and no. Oracle has a limit on the number of columns that can be returned by a query, so you probably can't do this in Oracle. – Gordon Linoff Mar 11 '20 at 12:37