Oracle PIVOT a select statement

Question

I would like to pivot a select statement. Columns "Country", "Store" and "Sales" are given.

Now I would like to have an output like:

         Store1  Store2 Store3
Country1   2342    2342   5675
Country2   5753    3274   7326
Country3   1543    4367   3367

So basically I need the salescount for every Store, for every Country.

The Input comes from (example):

Country:    StoreNr:    ProductSold:
 Belgium         23             Car
 Belgium         23           House
Netherland       23             Car

Output would be:

             Store23
Belgium            2
Netherlands        1

Answer 1

If the number of stores is finite, you could use one of these approaches:

1. Using count() aggregate function combined with case expression:

   -- sample of data. just for the sake of demonstration
   SQL> with t1(Country, StoreNr, ProductSold) as(
     2    select 'Belgium'   , 23,  'Car'   from dual union all
     3    select 'Belgium'   , 23,  'House' from dual union all
     4    select 'Netherland', 23,  'Car'   from dual union all
     5    select 'Belgium'   , 25,  'House' from dual
     6  )
     7  select country
     8       , count(case
     9                 when StoreNr = 23
    10                 then 1
    11               end) as storeNr_23
    12        , count(case
    13                 when StoreNr = 25
    14                 then 1
    15               end) as storeNr_25
    16    from t1
    17   group by country
    18  ;
   

   Result:

    COUNTRY    STORENR_23 STORENR_25
    ---------- ---------- ----------
    Belgium             2          1
    Netherland          1          0
   

2. Starting from Oracle 11g and up, the pivot operator as follows:

   select *
     from (Select country as country
                , country as country_cnt
                , StoreNr
            from t1)
    pivot(                                 -- list all store numbers here
        count(country_cnt) for storenr in (  23 as StoreNr_23
                                           , 25 as StoreNr_25) 
    )                                       
   

   Result:

    COUNTRY    STORENR_23 STORENR_25
    ---------- ---------- ----------
    Belgium             2          1
    Netherland          1          0