by WebSurfer
2010年8月7日 18:18
SQL Server の PIVOT 関係演算子を利用してデータをまとめ、クロス集計レポートを生成し、GridView に表示する例です。
ほとんどの処置はストアドプロシージャで行うので、ASP.NET というよりは SQL Server の機能になりますが。
ここの例では、Microsoft が提供しているサンプルデータベース Northwind の Orders テーブル使用しました。
Orders テーブルは、個々の注文の ID (OrderID, int)、注文した顧客の ID (CustomerID, nchar(5))、注文を取り扱った従業員の ID (EmployeeID, int)、注文を受けた年月日 (OrderDate, datetime) などのデータを約 800 レコード持っています。
DropDownList に顧客名一覧を表示し、その中から顧客を選択すると、その注文を取り扱った従業員と取り扱った注文数を、注文年月ごとに表示するようにしました。以下のような感じです。
上記のクロス集計を行うためのストアドプロシージャは以下の通りです。
ALTER PROCEDURE dbo.StoredProcedure1
(
@CustomerID nchar(5)
)
AS
DECLARE @sqlstr nvarchar(max), @sqldata nvarchar(max), @employee nvarchar(50);
DECLARE cur CURSOR FOR
SELECT DISTINCT EmployeeID
FROM Orders
WHERE EmployeeID IS NOT NULL AND [CustomerID]=@CustomerID
ORDER BY EmployeeID;
SET @sqldata = N'';
SET @sqlstr = N'SELECT Year, Month ';
OPEN cur;
FETCH NEXT FROM cur INTO @employee;
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
IF LEN(@sqldata) > 0
BEGIN
SET @sqldata = @sqldata + N','
END;
SET @sqldata = @sqldata + N'[' + @employee + N']'
SET @sqlstr = @sqlstr + N',[' + @employee + N'] AS [Emp-' + @employee + N']';
FETCH NEXT FROM cur INTO @employee;
END;
CLOSE cur;
DEALLOCATE cur;
SET @sqlstr = @sqlstr +
N' FROM
(
SELECT DATEPART(mm, OrderDate) AS Month, DATEPART(yyyy, OrderDate) AS Year, EmployeeID
FROM Orders
WHERE CustomerID=@ID
) AS p
PIVOT
(
COUNT(EmployeeID)
FOR EmployeeID
IN ';
SET @sqlstr = @sqlstr + N'(' + @sqldata + N')) AS pvt';
EXECUTE sp_executesql @sqlstr, N'@ID nchar(5)', @CustomerID
顧客一覧を DropDownList に、クロス集計結果を GridView に表示するための ASP.NET のコードは以下の通りです。
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>無題のページ</title>
</head>
<body>
<form id="form2" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="
SELECT DISTINCT Orders.CustomerID, Customers.CompanyName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID">
</asp:SqlDataSource>
<asp:DropDownList ID="DropDownList1"
runat="server"
DataSourceID="SqlDataSource1"
DataTextField="CompanyName"
DataValueField="CustomerID"
AutoPostBack="True">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="StoredProcedure1"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter
ControlID="DropDownList1"
Name="CustomerID"
PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1"
runat="server"
DataSourceID="SqlDataSource2">
</asp:GridView>
</div>
</form>
</body>
</html>