需求:依年份為列、月份為欄顯示金額加總。Access 專用 TRANSFORM ... PIVOT 語法可直接產生交叉表,避免程式迴圈組合。本文提供完整 SQL、資料範例、ASP 執行輸出 HTML 表格代碼。
| d_id | d_je (金額) | d_sxrq (日期) |
|---|---|---|
| 1 | 20 | 2009-5-1 |
| 2 | 30 | 2009-5-6 |
| 3 | 10 | 2009-6-5 |
| ... | ... | ... |
TRANSFORM Sum(IIF(Month(D_SXRQ) = 1, D_JE, 0)) AS M01,
Sum(IIF(Month(D_SXRQ) = 2, D_JE, 0)) AS M02,
...
Sum(IIF(Month(D_SXRQ) = 12, D_JE, 0)) AS M12
SELECT Year(D_SXRQ) AS Y
FROM D_FY
GROUP BY Year(D_SXRQ)
PIVOT Month(D_SXRQ);
※ Access 只允許單一彙總欄位於 TRANSFORM,故需用 IIF 拆解 12 個月份,或改用程式迴圈組合。
' 1. 取得年度清單
sqlYear = "SELECT DISTINCT Year(D_SXRQ) AS Y FROM D_FY ORDER BY Y DESC"
rsYear.Open sqlYear, conn
' 2. 取得明細資料 (年度、月份、金額)
sqlDetail = "SELECT Year(D_SXRQ) AS Y, Month(D_SXRQ) AS M, Sum(D_JE) AS Total " & _
"FROM D_FY GROUP BY Year(D_SXRQ), Month(D_SXRQ)"
rsDetail.Open sqlDetail, conn
' 3. 將明細存入 Dictionary (Key: "2024-1", Value: Total)
Dim dict, key
Set dict = Server.CreateObject("Scripting.Dictionary")
Do While Not rsDetail.EOF
key = rsDetail("Y") & "-" & rsDetail("M")
dict(key) = rsDetail("Total")
rsDetail.MoveNext
Loop
' 4. 輸出 HTML 表格
Response.Write "年份 "
For m = 1 To 12: Response.Write "" & m & "月 ": Next
Response.Write " "
Do While Not rsYear.EOF
y = rsYear("Y")
Response.Write "" & y & " "
For m = 1 To 12
key = y & "-" & m
If dict.Exists(key) Then
Response.Write "" & dict(key) & " "
Else
Response.Write "0 "
End If
Next
Response.Write " "
rsYear.MoveNext
Loop
Response.Write "
"
SELECT Y, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
FROM (
SELECT Year(D_SXRQ) AS Y, Month(D_SXRQ) AS M, D_JE
FROM D_FY
) AS SourceTable
PIVOT (SUM(D_JE) FOR M IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PivotTable
ORDER BY Y DESC;