当列名位于数据库而不是查询中时，如何在 SQL 中创建表格报告？

发布于 2024-07-29 13:51:00 字数 680 浏览 6 评论 0原文

http://www.geocities.com/colinpriley/sql/sqlitepg09.htm 有一种很好的技术来创建表格报告，其中表的列名称可以在查询中编码，但在我的例子中，列应该是数据库中的值。假设我有如下每日销售数据：

  Transaction  Date     Rep Product Amount
  1            July 1   Bob A12     $10
  2            July 2   Bob B24     $12
  3            July 2   Ted A12     $25
  ...

我想要一份每周汇总报告，显示每个代表销售的每种产品的数量：

        A12   B24
  Bob   $10   $12
  Ted   $25    $0

我的列名称来自“产品”列。比如说，任何在指定日期范围内具有行的产品都应该在报告中具有一列。但其他产品（在该时间范围内未售出）不应有一列全 0。我怎样才能做到这一点？如果它能在 SQLite 中运行，那就加分了。

TIA。

原文

http://www.geocities.com/colinpriley/sql/sqlitepg09.htm has a nice technique for creating a tabular report where the column names for the table can be coded in the query but in my case, the columns should be values from the database. Say I have daily sales figures like:

  Transaction  Date     Rep Product Amount
  1            July 1   Bob A12     $10
  2            July 2   Bob B24     $12
  3            July 2   Ted A12     $25
  ...

and I want a weekly summary report that shows how much of each product each rep sold:

        A12   B24
  Bob   $10   $12
  Ted   $25    $0

My column names come from the Product column. Say, any product that has a row in the specified date range should have a column in the report. But other products -- which weren't sold in that time frame -- should not have a column of all 0s. How can I do that? Bonus points if it works in SQLite.

TIA.

分享到QQ

分享到微博