0

## MSSQL PIVOT請益

### 1 個回答

1

iT邦新手 2 級 ‧ 2017-04-29 10:05:13

``````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

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 還方便.
``````