不一定要用 pivot

create table x170429 (
  store char(1) not null  
, mdate date not null  
, period char(3) not null
, guest int not null 
);


insert into x170429 (store, mdate, period, guest) values
('A', date '2017-04-01', 'T01', 0),
('A', date '2017-04-01', 'T02', 2),
('A', date '2017-04-01', 'T03', 6),
('A', date '2017-04-02', 'T01', 2),
('A', date '2017-04-02', 'T02', 2),
('A', date '2017-04-02', 'T03', 4),
('A', date '2017-04-03', 'T01', 1),
('A', date '2017-04-03', 'T02', 10),
('A', date '2017-04-03', 'T03', 0),
('B', date '2017-04-01', 'T01', 4),
('B', date '2017-04-01', 'T02', 2),
('B', date '2017-04-01', 'T03', 4),
('B', date '2017-04-02', 'T01', 3),
('B', date '2017-04-02', 'T02', 3),
('B', date '2017-04-02', 'T03', 4),
('B', date '2017-04-03', 'T01', 1),
('B', date '2017-04-03', 'T02', 7),
('B', date '2017-04-03', 'T03', 1);

-- 
create table x170429_aux (
  period char(3) not null
, col1 int
, col2 int 
, col3 int  
);

insert into x170429_aux values
('T01', 1, 0, 0),
('T02', 0, 1, 0),
('T03', 0, 0, 1);

---
select store
     , mdate
     , sum(T01) T01
     , sum(T02) T02
     , sum(T03) T03
  from (select a.store
             , a.mdate
             , a.guest * b.col1 T01
             , a.guest * b.col2 T02
             , a.guest * b.col3 T03
          from x170429 a
             , x170429_aux b
         where a.period = b.period) c
 group by store, mdate
 order by store, mdate
;

 store |   mdate    | t01 | t02 | t03 
-------+------------+-----+-----+-----
 A     | 2017-04-01 |   0 |   2 |   6
 A     | 2017-04-02 |   2 |   2 |   4
 A     | 2017-04-03 |   1 |  10 |   0
 B     | 2017-04-01 |   4 |   2 |   4
 B     | 2017-04-02 |   3 |   3 |   4
 B     | 2017-04-03 |   1 |   7 |   1
(6 筆資料列)

--- 延伸應用
alter table x170429_aux
  add column col4 int;

update x170429_aux
  set col4 = 1; 
  
select *
  from x170429_aux;
  
 period | col1 | col2 | col3 | col4 
--------+------+------+------+------
 T01    |    1 |    0 |    0 |    1
 T02    |    0 |    1 |    0 |    1
 T03    |    0 |    0 |    1 |    1
(3 筆資料列)

select store
     , mdate
     , sum(T01) T01
     , sum(T02) T02
     , sum(T03) T03
     , sum(Allday) "當日總和"
  from (select a.store
             , a.mdate
             , a.guest * b.col1 T01
             , a.guest * b.col2 T02
             , a.guest * b.col3 T03
             , a.guest * b.col4 Allday
          from x170429 a
             , x170429_aux b
         where a.period = b.period) c
 group by store, mdate
 order by store, mdate
;  

 store |   mdate    | t01 | t02 | t03 | 當日總和 
-------+------------+-----+-----+-----+----------
 A     | 2017-04-01 |   0 |   2 |   6 |        8
 A     | 2017-04-02 |   2 |   2 |   4 |        8
 A     | 2017-04-03 |   1 |  10 |   0 |       11
 B     | 2017-04-01 |   4 |   2 |   4 |       10
 B     | 2017-04-02 |   3 |   3 |   4 |       10
 B     | 2017-04-03 |   1 |   7 |   1 |        9
(6 筆資料列)

-- 這樣比pivot 還方便.