如何为两列制作两个透视格式

Question

我有这样的表

id | name  | date 1     | amount 1| date 2     | amount 2
---------------------------------------------------
1  | xxxx  | 01-02-2019 | 2000    | 02-04-2019 | 3500
2  | yyyy  | 01-04-2019 | 5000    | 01-06-2019 | 6800

我

只能为一列制作透视，我使用此代码

TRANSFORM Sum(tbl1.[amount1]) AS SumOfamount1
SELECT tbl1.[fname], Sum(tbl1.[amount1]) AS [Total Of amount1]
FROM tbl1
GROUP BY tbl1.[fname]
PIVOT Format([date1],"mmm") In 
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

此代码适用于日期 1 和金额 1是否也允许为 date2 和金额 2 制作？

Answer 1

尝试使用子查询：

TRANSFORM Sum(t.[amont1]) AS SumOfamont1
SELECT t.[fname],
       Sum(t.[amount1]) AS [Total Of amount1]
FROM (SELECT date1, amont1, fname
      FROM tbl1
      UNION ALL
      SELECT date2, amont2, fname
      FROM tbl1
     ) t      
GROUP BY t.[fname]
PIVOT Format([date1], "mmm") In ("Jan", "Feb", "Mar", "Apr", "May","Jun", "Jul", "Aug","Sep", "Oct", "Nov", "Dec");

如果这不起作用，您可能需要为union all使用视图。