iT邦幫忙

0

MSSQL PIVOT請益

http://ithelp.ithome.com.tw/upload/images/20170428/20104766xEbfaFvX10.png

請教邦友們
我有一MSSQL table原始資料如上圖
這張表為各店的每日營業時段客次

欲翻轉為
店名,日期,T01,T02,T03
將客次放入[T01][T02][T03]之中
如下圖
http://ithelp.ithome.com.tw/upload/images/20170428/20104766QtznrqAfNK.png

要如何保留店名、日期不翻轉
時段翻轉、客次翻轉為值的語法請益....

找了很多教學範例來改,都執行後錯誤,可能是我使用方法不太對

接近 'PIVOT' 的語法錯誤。您可能要將目前資料庫的相容性層級設成高一點的值,以啟用這項功能。

1 個回答

1
一級屠豬士
iT邦新手 3 級 ‧ 2017-04-29 10:05:13
最佳解答

不一定要用 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 還方便.

謝謝,以經試出來了

我要發表回答

立即登入回答