交叉資料表查詢中的多列顯示

利用交叉表，我們可以對某個欄欄位的項目進行顯示，比如[學科]來顯示[考試成績]，那麼是否能同時顯示其[作業成績]在同一個交叉資料表查詢中？

比如下面資料
Table3
+-----+--------+-------+-------+------+-------------+
|id   |sName   |sClass |Course |Score |homeworkScore|
+-----+--------+-------+-------+------+-------------+
|1    |AAA     |3      |語文   |50    |8            |
|2    |AAA     |3      |數學   |83    |9            |
|3    |AAA     |3      |英語   |65    |7            |
|4    |BBB     |3      |語文   |86    |6            |
|5    |BBB     |3      |數學   |95    |5            |
.......
|30   |JJJJ    |5      |英語   |61    |9            |
|31   |LL      |5      |語文   |80    |7            |
|32   |LL      |5      |數學   |95    |9            |
+-----+--------+-------+-------+------+-------------+

我們可以得到關於科目的交叉資料表查詢 [考試成績],也可以得到[作業成績]

TRANSFORM Sum(Table3.Score)
SELECT Table3.sName, Table3.sClass
FROM Table3
GROUP BY Table3.sName, Table3.sClass
PIVOT Table3.Course;

+-------+------+----+----+----+
|sName  |sClass|數學|英語|語文|
+-------+------+----+----+----+
|AAA    |3     |83  |65  |50  |
|BBB    |3     |95  |58  |86  |
|CCC    |3     |75  |78  |92  |
|DDD    |3     |76  |77  |83  |
.....
|JJJJ   |5     |97  |61  |62  |
|LL     |5     |95  |    |80  |
+-------+------+----+----+----+

TRANSFORM Sum(Table3.homeworkScore)
SELECT Table3.sName, Table3.sClass
FROM Table3
GROUP BY Table3.sName, Table3.sClass
PIVOT Table3.Course;
+-------+------+----+----+----+
|sName  |sClass|數學|英語|語文|
+-------+------+----+----+----+
|AAA    |3     |8   |6   |8   |
|BBB    |3     |6   |7   |7   |
|CCC    |3     |7   |9   |9   |
.....
|JJJJ   |5     |9   |7   |5   |
|LL     |5     |7   |    |8   |
+-------+------+----+----+----+

那麼如何把這兩項合并顯示呢？

首先容易想到的方法是，將這兩個單獨的交叉資料表查詢儲存了各自的查詢,比如query1,query2,然後再形成基於這兩個 query1,query2 的 inner join 查詢。
SELECT Query1.*, Query2.*
FROM Query1 INNER JOIN Query2 ON Query1.sName = Query2.sName;

由於 交叉資料表查詢 的列數我們無法預知，所以只能選擇 Query1.*, Query2.* 所有的欄位。結果如下
+--------+---------+-------+-------+-------+--------+---------+-------+-------+-------+
|Q1.sName|Q1.sClass|Q1.數學|Q1.英語|Q1.語文|Q2.sName|Q2.sClass|Q2.數學|Q2.英語|Q2.語文|
+--------+---------+-------+-------+-------+--------+---------+-------+-------+-------+
|AAA     |3        |83     |65     |50     |AAA     |3        |8      |6      |8      |
|BBB     |3        |95     |58     |86     |BBB     |3        |6      |7      |7      |
|CCC     |3        |75     |78     |92     |CCC     |3        |7      |9      |9      |
|DDD     |3        |76     |77     |83     |DDD     |3        |7      |6      |9      |
|EEE     |3        |76     |58     |60     |EEE     |3        |5      |8      |5      |
|FFF     |4        |62     |60     |85     |FFF     |4        |7      |7      |7      |
|GGG     |4        |80     |97     |81     |GGG     |4        |6      |10     |8      |
|HHHH    |4        |88     |50     |74     |HHHH    |4        |8      |9      |8      |
|IIIIIII |4        |68     |50     |95     |IIIIIII |4        |5      |7      |8      |
|JJJJ    |5        |97     |61     |62     |JJJJ    |5        |9      |7      |5      |
|LL      |5        |95     |       |80     |LL      |         |7      |       |8      |
+--------+---------+-------+-------+-------+--------+---------+-------+-------+-------+

有沒有其它的方法？
如果使用者的需求只是為了顯示，（交叉資料表查詢好象也只能為了顯示，無法更新，想不出還有什麼用途）, 則可以通過字串合併作業把多個欄位合并為一個欄位。如下 (由於是字串，這裡改sum()為min())

TRANSFORM min(Table3.Score & ' + ' & Table3.homeworkScore)
SELECT Table3.sName, Table3.sClass
FROM Table3
GROUP BY Table3.sName, Table3.sClass
PIVOT Table3.Course;

+-------+------+-------+-------+-------+
|sName  |sClass|數學   |英語   |語文   |
+-------+------+-------+-------+-------+
|AAA    |3     |83 + 8 |65 + 6 |50 + 8 |
|BBB    |3     |95 + 6 |58 + 7 |86 + 7 |
|CCC    |3     |75 + 7 |78 + 9 |92 + 9 |
|DDD    |3     |76 + 7 |77 + 6 |83 + 9 |
|EEE    |3     |76 + 5 |58 + 8 |60 + 5 |
|FFF    |4     |62 + 7 |60 + 7 |85 + 7 |
|GGG    |4     |80 + 6 |97 + 10|81 + 8 |
|HHHH   |4     |88 + 8 |50 + 9 |74 + 8 |
|IIIIIII|4     |68 + 5 |50 + 7 |95 + 8 |
|JJJJ   |5     |97 + 9 |61 + 7 |62 + 5 |
|LL     |5     |95 + 7 |       |80 + 8 |
+-------+------+-------+-------+-------+

如果還要計算，比如匯入到EXCEL中，則需要把多列按 交叉資料表查詢 的要求整理一下資料來源。

select sName,sClass,Course & ':考試成績' as sColID,Score as vCol
from Table3
UNION ALL
select sName,sClass,Course & ':作業成績' as sColID,homeworkScore as vCol
from Table3
+-----+------+--------------+-----+
|sName|sClass|sColID        |vCol |
+-----+------+--------------+-----+
|AAA  |3     |數學:考試成績 |83   |
|AAA  |3     |英語:考試成績 |65   |
|BBB  |3     |語文:考試成績 |86   |
|BBB  |3     |數學:考試成績 |95   |
|AAA  |3     |語文:考試成績 |50   |
|AAA  |3     |數學:作業成績 |8    |
|AAA  |3     |英語:作業成績 |6    |
|BBB  |3     |語文:作業成績 |7    |
|BBB  |3     |數學:作業成績 |6    |
........                  
|LL   |5     |語文:作業成績 |8    |
|LL   |5     |數學:作業成績 |7    |
+-----+------+--------------+-----+

然後再基於此做交叉查詢.
TRANSFORM Sum(t.vCol)
SELECT t.sName, t.sClass
FROM (
select sName,sClass,Course & ':考試成績' as sColID,Score as vCol
from Table3
UNION ALL
select sName,sClass,Course & ':作業成績' as sColID,homeworkScore as vCol
from Table3
)t
GROUP BY t.sName, t.sClass
PIVOT t.sColID;

+-------+------+-------------+-------------+-------------+-------------+-------------+-------------+
|sName  |sClass|數學:作業成績|數學:考試成績|英語:作業成績|英語:考試成績|語文:作業成績|語文:考試成績|
+-------+------+-------------+-------------+-------------+-------------+-------------+-------------+
|AAA    |3     |8            |83           |6            |65           |8            |50           |
|BBB    |3     |6            |95           |7            |58           |7            |86           |
|CCC    |3     |7            |75           |9            |78           |9            |92           |
|DDD    |3     |7            |76           |6            |77           |9            |83           |
|EEE    |3     |5            |76           |8            |58           |5            |60           |
|FFF    |4     |7            |62           |7            |60           |7            |85           |
|GGG    |4     |6            |80           |10           |97           |8            |81           |
|HHHH   |4     |8            |88           |9            |50           |8            |74           |
|IIIIIII|4     |5            |68           |7            |50           |8            |95           |
|JJJJ   |5     |9            |97           |7            |61           |5            |62           |
|LL     |5     |7            |95           |             |             |8            |80           |
+-------+------+-------------+-------------+-------------+-------------+-------------+-------------+

本文只是探討了一種在ACCESS中如何?多列資料同樣在交叉查詢表中實現的方法。當然還有很多方法。