我想要一个表格格式的选择查询结果，如摘要报告

2021-09-16 00:00:00 sql vb.net sql-server-2008 sql-server

例如

           month1  month2 month3  total
district1    5       2      9       16
district2    1       0      11      12
    .
    .

 total        260    150     140    550

这里的最终总数并不重要.但至少我需要每月显示每个地区的数量.

here final total is not much important. but at least i need to show count per district per month. 

SELECT Districts_mst.district_name,COUNT(Payments.PaymentId)users ,DATEPART(M,payments.saveon)Month
FROM Payments
JOIN Subsciber ON Payments.SubId =Subsciber.SubId
JOIN districts_mst ON districts_mst.district_id = Subsciber.District
where lang_id=1
group by district_name, DATEPART(M,payments.saveon)

其中给我的清单就像.....

which give me list like.....

district_name   users   Month 
dist0           1       1 
dist1           1       11 
dist2           3       11 
dist3           1       11 
dist4           3       11 
dist5           1       12 
dist6           1       12

推荐答案

在 SQL Server 2008 中，您可以使用 PIVOT 查询非常轻松地处理此任务.以下示例依赖于将您的数据转换为以下格式(看起来您已经完成了):

In SQL Server 2008 you can handle this task pretty easily with a PIVOT query. The following example relies on getting your data into the following format (which it looks like you have done already):

Name        Month    Value
----------  -------  -----
District 1  Month 1     10
District 1  Month 2      5
District 1  Month 3      6
District 2  Month 1      1
District 2  Month 2      2
District 2  Month 3      3
District 3  Month 1      8
District 3  Month 2      6
District 3  Month 3     11

如果您可以这样做，那么您的 PIVOT 查询应如下所示:

If you can do that, then your PIVOT query should look something like this:

DECLARE @myTable AS TABLE([Name] VARCHAR(20), [Month] VARCHAR(20), [Value] INT)
INSERT INTO @myTable VALUES ('District 1', 'Month 1', 10)
INSERT INTO @myTable VALUES ('District 1', 'Month 2', 5)
INSERT INTO @myTable VALUES ('District 1', 'Month 3', 6)
INSERT INTO @myTable VALUES ('District 2', 'Month 1', 1)
INSERT INTO @myTable VALUES ('District 2', 'Month 2', 2)
INSERT INTO @myTable VALUES ('District 2', 'Month 3', 3)
INSERT INTO @myTable VALUES ('District 3', 'Month 1', 8)
INSERT INTO @myTable VALUES ('District 3', 'Month 2', 6)
INSERT INTO @myTable VALUES ('District 3', 'Month 3', 11)

SELECT [Name], [Month 1], [Month 2], [Month 3], [NameTotalValue] AS [Total]
FROM
(
      SELECT [Name], [Month], [Value], 
             SUM([Value]) OVER (PARTITION BY [Name]) as [NameTotalValue] 
      FROM @myTable
    UNION 
      SELECT 'Total', [Month], SUM([Value]), (SELECT SUM([Value]) FROM @myTable)
      FROM @myTable
      GROUP BY [Month]
) t
PIVOT
(
    SUM([Value]) FOR [Month] IN ([Month 1], [Month 2], [Month 3]) 
) AS pvt
ORDER BY pvt.[Name]

在这个例子中，我使用 SUM([Value]) OVER PARTITION 来获取每个区的总和，然后我做了一个 UNION 来在底部添加一个总计行.结果如下:

In this example, I used the SUM([Value]) OVER PARTITION to get the sums for each District, and then I did a UNION to add a totals row to the bottom. The results look like this:

Name        Month 1 Month 2 Month 3 Total
----------- ------- ------- ------- -----
District 1       10       5       6    21
District 2        1       2       3     6
District 3        8       6      11    25
Total            19      13      20    52

关于这种方法，您会注意到的一件事是您必须提前知道您想要在表格顶部显示的列名称.如果您将报告设置为运行一整年，这很容易做到，但如果列数将发生变化，则比较棘手.如果您要允许用户指定自定义日期范围(即 07/2011-10/2011 或 06/2011-11/2011)，则处理该要求的一种方法是使用动态 SQL 构建 PIVOT 查询然后使用 sp_executesql 执行它.

One thing you'll notice about this approach is that you have to know the column names you want at the top of the table ahead of time. That's easy to do if you're setting up the report to run for a full year, but is trickier if the number of columns is going to change. If you're going to allow the users to specify a custom date range (i.e., 07/2011-10/2011 or 06/2011-11/2011), then one way handle that requirement is to build the PIVOT query using dynamic SQL and then execute it with sp_executesql.

相关文章