問題描述
例如
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.
這篇關于我想要一個表格格式的選擇查詢結果，如摘要報告的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！