トップ回答者
動的に作成するGridViewとDropDownListを連動させる方法

質問
-
たびたびお世話になります。
先日教えていただいた以下のGridViewを作成するSQLコマンドを応用してDropDownListから会社名を選ぶと、該当するデータを下にGridViewを作成するように書き換えようとしているのですが、理解が足りずぜんぜんうまくいきません。
どこに WHERE (([Company] = @Company) を追加すればよいのか教えていただけないでしょうか?
よろしくお願いします。
<asp
qlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:usagelogConnectionString1 %>"
SelectCommand="BEGIN DECLARE @sqlstr varchar(max), @sqldatas varchar(max), @targetKyoku varchar(50); DECLARE cur CURSOR FOR SELECT DISTINCT _15 FROM UsageLog_past12Ms WHERE _15 IS NOT NULL ORDER BY _15 ASC; SET @sqldatas = ''; SET @sqlstr = 'SELECT tuki'; OPEN cur; FETCH next FROM cur INTO @targetKyoku; WHILE (@@fetch_status <> - 1) BEGIN IF len(@sqldatas) > 0 BEGIN SET @sqldatas = @sqldatas + ',' END; SET @sqldatas = @sqldatas + '[' + @targetKyoku + ']' SET @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'; FETCH next FROM cur INTO @targetKyoku; END; CLOSE cur; DEALLOCATE cur; SET @sqlstr = @sqlstr + ' FROM (select _11 as tuki, _15 FROM UsageLog_past12Ms) T PIVOT (Count([_15]) FOR [_15] IN '; SET @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'; EXECUTE (@sqlstr); END;"><SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="Company"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</aspqlDataSource>
回答
-
例です。
CREATE PROCEDURE [test].[PivotTest]
(
@company nvarchar(10) = ''
)
as
begin
declare
@sqlstr nvarchar(max),
@sqldatas nvarchar(max),
@targetKyoku nvarchar(10)
declare cur cursor for
SELECT DISTINCT _15 FROM UsageLog_past12Ms where _15 is not null and company = case when @company != '' then @company else _15 end
set @sqldatas = ''
set @sqlstr = 'SELECT tuki'
open cur
if @@CURSOR_ROWS = 0
begin
close cur
deallocate cur
return
end
fetch next from cur into @targetKyoku
while (@@fetch_status <> -1 )
begin
if len(@sqldatas) > 0
begin
set @sqldatas = @sqldatas + ','
end
set @sqldatas = @sqldatas + '[' + @targetKyoku + ']'
set @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'
fetch next from cur into @targetKyoku
end
close cur
deallocate cur
set @sqlstr = @sqlstr + ' FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms) T PIVOT (Count([_15]) FOR [_15] IN '
set @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'
exec sp_executesql @sqlstr, N'@company nvarchar(10)', @company
end
すべての返信
-
早速ありがとうございます。
_15 = @Companyを足して実行するとオブジェクト参照がオブジェクト インスタンスに設定されていませんとなりGridViewが失敗します。_15という列とは別にCompanyという列が元にしているテーブルにあって選んだ会社名分のデータを下に集計結果をGridVIewに表示させたいのです。よくわからないのですが、Company=@Company
としても同様にエラーになってしまいます。
どうすればよいのでしょうか?教えてください。
-
SQLはCompany=@Companyで問題ありません。問題はGridViewなのかSQLなのかを切り分けるためにも、SQLの部分を可能であればストアドプロシージャの形にしてしまうのが良いと思います。
今回はオブジェクトのインスタンスの問題ですので、たぶんSQL以外の問題だと思います。もう少し詳しいエラーメッセージを教えて下さい。
-
お世話になります。
それがDropDownListと連動しないようにして表示させるとちゃんと5列で数十ページのデータが表示されます。
DrowDownListの規定値がNULLなのかもしれないと思いDefaultVlaueを以下のように設定してみたのですがやはり同じです。
<asp:ControlParameter ControlID="DropDownList1"
DefaultValue="Existing Company Name Inc." Name="Company" PropertyName="SelectedValue"
Type="String" />ちょっと長いので心配ですがあとでソースコードを貼り付けますので引き続き、
よろしくお願いします。
-
<%@ Page validaterequest="false" Language="VB" MasterPageFile="~/Pages/MasterPage.master" AutoEventWireup="false" CodeFile="report.aspx.vb" Inherits="Pages_Statistics" title="無題のページ" %>
<asp:Content ID="Content1" runat="server"
contentplaceholderid="ContentPlaceHolder2">
<SCRIPT LANGUAGE="Javascript" SRC="../FusionCharts/FusionCharts.js"></SCRIPT>
<head>
<style type ="text/css">
th.Tategaki { writing-mode: tb-rl; }
</style>
</head>
<aspropDownList ID="DropDownList1" runat="server"
DataSourceID="SqlDataSource3" DataTextField="Company"
DataValueField="Company" >
</aspropDownList>
期間:<aspropDownList ID="DropDownList2" runat="server"
DataSourceID="SqlDataSource5" DataTextField="_1"
DataTextFormatString="{0:yyyy年M月}" DataValueField="_1"
AutoPostBack="False">
</aspropDownList>から<asp
ropDownList ID="DropDownList3" runat="server"
DataSourceID="SqlDataSource6" DataTextField="_1"
DataTextFormatString="{0:yyyy年M月}" DataValueField="_1"
AutoPostBack="False">
</aspropDownList>
<br />
<br />
<br />
<%=CreateChart()%> <br />
<asp:GridView ID="GridView1" runat="server"
DataSourceID="SqlDataSource1" PageSize="20" style="text-align: center"
Width="80%" AllowSorting="True"
EnableSortingAndPagingCallbacks="True" AllowPaging="True"
HorizontalAlign="Center">
<HeaderStyle
BorderColor="#666699" />
</asp:GridView>
<asp:Label ID="Label1" runat="server" Text="Label" Width="100%"></asp:Label>
<br />
<br />
<aspqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:usagelogConnectionString11 %>"
SelectCommand="BEGIN DECLARE @sqlstr varchar(max), @sqldatas varchar(max), @targetKyoku varchar(50); DECLARE cur CURSOR FOR SELECT DISTINCT _15 FROM UsageLog_past12Ms WHERE _15 IS NOT NULL AND [Company] =@Company ORDER BY _15 ASC; SET @sqldatas = ''; SET @sqlstr = 'SELECT tuki'; OPEN cur; FETCH next FROM cur INTO @targetKyoku; WHILE (@@fetch_status <> - 1) BEGIN IF len(@sqldatas) > 0 BEGIN SET @sqldatas = @sqldatas + ',' END; SET @sqldatas = @sqldatas + '[' + @targetKyoku + ']' SET @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'; FETCH next FROM cur INTO @targetKyoku; END; CLOSE cur; DEALLOCATE cur; SET @sqlstr = @sqlstr + ' FROM (select _11 as tuki, _15 FROM UsageLog_past12Ms) T PIVOT (Count([_15]) FOR [_15] IN '; SET @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'; EXECUTE (@sqlstr); END;">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="Company"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</aspqlDataSource>
<aspqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
SelectCommand="SELECT [Company] FROM [UsageLog_past12Ms] WHERE ([Company] = @Company)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1"
Name="Company" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</aspqlDataSource>
<aspqlDataSource ID="SqlDataSource5" runat="server"
ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
SelectCommand="SELECT DISTINCT _1 FROM UsageLog_past12Ms ORDER BY _1 ASC">
</aspqlDataSource>
<aspqlDataSource ID="SqlDataSource6" runat="server"
ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
SelectCommand="SELECT DISTINCT _1 FROM UsageLog_past12Ms ORDER BY _1">
</aspqlDataSource>
<p>
</p>
</asp:Content> -
Imports System
Imports System.IO
Imports InfoSoftGlobal
Partial Class Pages_Statistics
Inherits System.Web.UI.Page
Public Function CreateChart() As StringDim strXML As String
Dim StartRow As Integer
Dim EndRow As Integer
StartRow = 1
EndRow = GridView1.Rows.Count - 1
strXML = ""
strXML = strXML & "<graph caption='' subCaption='' borderColor = 'E7E7EF' bgColor ='E7E7EF' bgAlpha='0' canvasBorderColor='E7E7EF' numdivlines='10' lineThickness='1' showValues='0' numVDivLines='11' formatNmberScale='0' labelDisplay='ROTATE' slantLabels='1' anchorRadius='2' anchorBgAlpha='0' showAlternateVGridColor='1' anchorAlpha='100' animation='1' limitsDecimalPrecision='0' divLineDecimalPrecision='1'>" & "<categories>"For i = 1 To 5 Step 1
strXML = strXML & "<category label='"
If Me.GridView1.HeaderRow.Cells(i).HasControls Then
strXML = strXML & " " & Left(DirectCast(Me.GridView1.HeaderRow.Cells(i).Controls(0), Web.UI.WebControls.LinkButton).Text, 10)
Else
strXML = strXML & " " & Me.GridView1.HeaderRow.Cells(i).Text
End If
strXML = strXML & "'" & "/>"Next
strXML = strXML & "</categories>"For j = StartRow To EndRow Step 1
strXML = strXML & "<dataset seriesName= '" & GridView1.Rows.Item(j).Cells(0).Text & j & "'>"For k = 1 To GridView1.HeaderRow.Cells.Count - 1 Step 1
strXML = strXML & "<set value='" & GridView1.Rows.Item(j).Cells(k).Text & "'/>"
Next
strXML = strXML & "</dataset>"
Next
strXML = strXML & "</graph>"
Return FusionCharts.RenderChart("../FusionCharts/MSArea.swf", "", strXML, "myNext", "900", "400", False, True)
End Function
Protected Sub Page_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadComplete
Label1.Text = GridView1.Rows.Count
End SubProtected Sub GridView1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.PreRender
Dim myStyle As Style = New Style
myStyle.CssClass = "Tategaki"
For z = 1 To GridView1.HeaderRow.Cells.Count - 1 Step 1
Me.GridView1.HeaderRow.Cells(z).ApplyStyle(myStyle)
Next
End SubEnd Class
-
問題とは関係ないかもしれませんが、このあたり、どう考えても変なんですけど・・・
DropDownList にはちゃんと表示がされるのでしょうか?
Code Snippet<asp:DropDownList
ID="DropDownList1"
runat="server"
DataSourceID="SqlDataSource3"
DataTextField="Company"
DataValueField="Company" >
</asp:DropDownList><asp:SqlDataSource
ID="SqlDataSource3"
runat="server"
ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
SelectCommand="SELECT [Company] FROM [UsageLog_past12Ms] WHERE ([Company] = @Company)">
<SelectParameters>
<asp:ControlParameter
ControlID="DropDownList1"
Name="Company"
PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource> -
こんな感じじゃないですか?
Code Snippet<asp:sqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
SelectCommand="SELECT DISTINCT [Company] FROM [UsageLog_past12Ms] ORDER BY [Company]">
</asp:sqlDataSource>#SurferOnWwwさんとかぶりました。同じようなことですね。
-
すみません。
いろいろと試行錯誤中に混乱して書き込んでしまっていたことがわかりました。
この部分を正しく直してもやはりずっと一貫して同じエラーがでますね。
よろしくお願いします。
<asp
qlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:usagelogConnectionString13 %>"
SelectCommand="SELECT DISTINCT [Company] FROM [UsageLog_past12Ms] ORDER BY [Company]">
</aspqlDataSource>
-
「一貫して同じエラー」、「同じ問題」というのは、
> 行 62: For z = 1 To GridView1.HeaderRow.Cells.Count - 1 Step 1
のところで、
> オブジェクト参照がオブジェクト インスタンスに設定されていません
というエラーメッセージが出るということだと思いますが、とすると、
どっとねっとふぁんさんの指摘、> 抽出されたデータが0件だったりしませんか?
が当たっているような気がします。その点はチェックされたでしょうか?
DropDownList で選んだ Company は SqlDataSource1 の SelectParameters
として渡されているが、クエリに問題があって何も抽出されないというのが
怪しいと思います。クエリを見せてもらっても自分には分からないので根拠
なしですけど・・・でも、_15 と Company は同じ内容とのことで、とすると少なくとも以下は
変だと思うのですが・・・SELECT DISTINCT _15 FROM UsageLog_past12Ms WHERE _15 IS NOT NULL AND Company=@Company ORDER BY _15 ASC
-
失礼しました。いろいろと試行錯誤をして適当に書き換えているときに誤って書き加えてしまったとことでした。
直してもやはり同じエラーです。
なお、同じようなページを軸を変えたりしていくつも作ったのでそのページでWHERE句のところにいろいろ足して試したところ、
WHERE _1 IS NOT NULL AND WHERE [_15] <> 'NTT' NTTが実際にデータに存在するとエラーになる。
WHERE _1 IS NOT NULL AND WHERE [_15] <> 'NTO' ちょっと変えて存在しない文字に置き換えると問題なく表示される。
WHERE _1 IS NOT NULL AND COMPANY <> 'NTT DATA CO LITD' 実在するが成功する。
WHERE _1 IS NOT NULL AND COMPANY = 'NTT DATA CO LITD' =に変更すると失敗する。
こんな感じなのですがデータがおかしいのでしょうか?
あるいはExpress Editionだからでしょうか?
よろしくお願いします。
-
tosaito さんからの引用 WHERE _1 IS NOT NULL AND WHERE [_15] <> 'NTT' NTTが実際にデータに存在するとエラーになる。
WHERE _1 IS NOT NULL AND WHERE [_15] <> 'NTO' ちょっと変えて存在しない文字に置き換えると問題なく表示される。
WHERE _1 IS NOT NULL AND COMPANY <> 'NTT DATA CO LITD' 実在するが成功する。
WHERE _1 IS NOT NULL AND COMPANY = 'NTT DATA CO LITD' =に変更すると失敗する。
WHERE _1 は _15の誤りですよね?
確かに0件の時にこけるので、open cur;の後に、以下の文を入れてみて下さい。
if @@CURSOR_ROWS = 0 return;
-
ありがとうございます。
_15の間違いでした。if @@CURSOR_ROWS = 0 return;を追加してもDropDownListと連動しているほうはだめで、オブジェクト参照がオブジェクト インスタンスに設定されていませんとなりますが、WHERE _15 <> 'NTT' のほうはエラーは発生せず一応ページが表示されました。しかし、除外したはずのNTTの行が残っており、逆に列の年月の方が1列だけになりました。
tsuki 4 2008 5 2008 ←(このヘッダー部分が_1)
NTT 3 4
KDDI 2 3
↑(この列が_15)
WHEREを入れる場所が違うのでしょうか?
よろしくお願いします。
-
最新のクエリ(SqlDataSource の SelectCommand)と UsageLog_past12Ms
テーブル/データの構造を教えてもらえませんか?
問題は一つだけでなく、いろいろあるようです。DropDownList と GridView
のみのシンプルなコードで試してみることをお勧めします(ヌル参照エラー
を出しているハンドラはとりあえず削除して試してみるとか)。
> WHEREを入れる場所が違うのでしょうか?
問題とは関係ないかもしれませんが、DropDownList で選んだ Company
のみがアクセスした各ジャンルの月別のアクセス回数を表示するということ
であれば、EXECUTE (@sqlstr) の @sqlstr の中にも WHERE の条件を
入れる必要があるはずです。(テーブル/データ構造が分からないのではっき
りしたことは言えませんが)
-
tosaito さんからの引用 tsuki 4 2008 5 2008 ←(このヘッダー部分が_1)
NTT 3 4
KDDI 2 3
いつの間にか行と列が入れ替わっていませんか?
ところでSQL Serverをお使いだと思いますので、問題を切り分けるためにもストアドプロシージャ化しませんか? そうすればまずストアドプロシージャが正しく動くかどうかを確かめられます。ストアドプロシージャが正しく動けばGridViewの表示に問題があることになります。プログラムが複雑化するほど一気に正しく動かすのは難しくなりますので、どこまでが正しく動くのかを切り分けながら進むことがとても大事です。
SQL Server Management Studioで問題のSQL文を実行し、正しく動作するかを確かめた方が良いと思います。私も複雑なSQL文の場合は、それが正しく動作することを確認してからアプリケーションを組み込みます。
-
ちょっと返信が遅くなってしまいまして申し訳ありません。
SQL Server 2005 Express Editionなのですが、調べてみてストアドプロシージャにできるようであれば来週いろいろと調べて挑戦してみようと思います。行と列が入れ替わっているのは、同じようなページをいくつも作っていて、そのうちのひとつでした。まぎらわしくてすみません。
なお、この例ではためしにWhereでPullDownListの値で抽出せず、シンプルにたての項目の中でひとつだけ除外 Where _15 <>"XXX" としようとしてもエラーになってしまい、if @@CURSOR_ROWS = 0 returnを追加するとエラーは発生しないのですが、こんどはWhere _15<>"XXX"としたはずなのに除外されていないという結果でした。
ちょっともう一度情報を整理してみます。
引き続きよろしくお願いします。
-
tosaito さんからの引用 SQL Server 2005 Express Editionなのですが、調べてみてストアドプロシージャにできるようであれば来週いろいろと調べて挑戦してみようと思います。
SQL Server 2005 Express Editionはストアドプロシージャをサポートしています。SQL Serever Management Studio Express Editionをお持ちでなければ、これも使われると良いでしょう。
さらにプロファイラも以下にフリーであります。プロファイラは実際にどのようなSQL文が投げられているのか確認する時に便利です。
Profiler for Microsoft SQL Server 2005 Express Edition
http://sqlprofiler.googlepages.com/(参考)
SQL Server 2005 Express Editionに発行されたSQL文をトレースするには?
http://www.atmarkit.co.jp/fdotnet/dotnettips/744sqlexpressprofiler/sqlexpressprofiler.html
-
お返事が遅くなりまして申し訳ありません。
テーブル構造とSQLコマンドはこのような感じになっていますが、名前がわかりにくいのでわかりやすく書き換えてシンプルなサンプルページを作成してできれば今週中に投稿しようと思います。
_1(datetime) 年月 (例:2008年1月)
Company(nvarchar50) 会社名(NTTデータ株式会社)
Recipient(nvarchar50) 契約者(山田太郎)
_7(datetime)アクセス日時(2008/1/1 11:22:11)
DocTitle(nvarchar50) アクセスしたレポートのタイトル
_15(nvarchar50) レポートのカテゴリー
PUBLICATIONDATE(datetime) レポートの発行日
SelectCommand="BEGIN DECLARE @sqlstr varchar(max), @sqldatas varchar(max), @targetKyoku varchar(50); DECLARE cur CURSOR FOR SELECT DISTINCT _1 FROM UsageLog_past12Ms WHERE _15 <> 'XXX' ORDER BY _1 ASC; SET @sqldatas = ''; SET @sqlstr = 'SELECT tuki'; OPEN cur; if @@CURSOR_ROWS = 0 return; FETCH next FROM cur INTO @targetKyoku; WHILE (@@fetch_status <> - 1) BEGIN IF len(@sqldatas) > 0 BEGIN SET @sqldatas = @sqldatas + ',' END; SET @sqldatas = @sqldatas + '[' + @targetKyoku + ']' SET @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'; FETCH next FROM cur INTO @targetKyoku; END; CLOSE cur; DEALLOCATE cur; SET @sqlstr = @sqlstr + ' FROM (select _15 as tuki, _1 FROM UsageLog_past12Ms) T PIVOT (Count([_1]) FOR [_1] IN '; SET @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'; EXECUTE (@sqlstr); END;">
この場合、_1(年月)がHeaderRowに順番に入り_15(カテゴリー)が縦になります。
ぜんぜんSQL文の内容は理解できていないのですが、列と行の部分だけを差し替えるだけで簡単に集計データが作成できて非常に便利なのでコピペして乱用しています。 WHEREで対象になるデータの絞込みができるようになればさらに便利になると思います。
-
tosaito さんからの引用 テーブル構造とSQLコマンドはこのような感じになっていますが、名前がわかりにくいのでわかりやすく書き換えてシンプルなサンプルページを作成してできれば今週中に投稿しようと思います。
_1(datetime) 年月 (例:2008年1月)
Company(nvarchar50) 会社名(NTTデータ株式会社)
Recipient(nvarchar50) 契約者(山田太郎)
_7(datetime)アクセス日時(2008/1/1 11:22:11)
DocTitle(nvarchar50) アクセスしたレポートのタイトル
_15(nvarchar50) レポートのカテゴリー
PUBLICATIONDATE(datetime) レポートの発行日
回答者が知らないところでいろいろ変わっているようですが、とりあえず上記のテ
ーブル/データ構造はこのスレッドでは変えないと言うことにできますか?
それで、このデータから、どの項目を、どのような条件で抽出して、GridView にど
のように表示したいかを明確に書いていただけませんか?
上記の両方とも、このスレッドでは変えないということで話をしないと、回答者はつ
いていけません。
[qoute user="tosaito"]
ぜんぜんSQL文の内容は理解できていないのですが、列と行の部分だけを差し替えるだけで簡単に集計データが作成できて非常に便利なのでコピペして乱用しています。
はっきり言ってそれではダメです。 tosaito さんも回答者も混乱するばかりで
いつまでたってもラチがあきません。回答者はエスパーではないのですから、
質問者しか知りえない情報は分かりませんし、回答者の知らないところで以前
の条件等を変えられても分かりません。
-
いろいろと混乱を生じさせてしまいまして申し訳ありません。このテーブル・データ構造はこれで固定にします。
カテゴリーにNHKなどと会社名が入っているのでそれも混乱の原因ではないかと思いますが、実はテーブル構造は一切変更していません。WHEREの条件のところを当初はWHERE Company=@Company としてDropDownlistの値と連動させていたのですが、WHERE _15<> "XXX"とシンプルな条件にしてみても同様に失敗するので、DropDownListとの連携以前にそのシンプルなケースを調べれば原因がわかるのではないかと考えていました。
ということで、上記テーブル構造で固定ということにして何をしたいか・何がわからないかをなるべくシンプルにわかりやすく書いてみます。
まず、すべてのデータからWHERE _15 <> "INF"の条件に当てはまる分だけ抽出したい。
それからその抽出したデータに対して例の以下のPIVOTのSQL文を実行したい。
つまりはPIVOTを実行する対象を絞り込むWHERE句を書き込む場所がわからない。
SelectCommand="BEGIN DECLARE @sqlstr varchar(max), @sqldatas varchar(max), @targetKyoku varchar(50); DECLARE cur CURSOR FOR SELECT DISTINCT _1 FROM UsageLog_past12Ms ORDER BY _1 ASC; SET @sqldatas = ''; SET @sqlstr = 'SELECT tuki'; OPEN cur; if @@CURSOR_ROWS = 0 return; FETCH next FROM cur INTO @targetKyoku; WHILE (@@fetch_status <> - 1) BEGIN IF len(@sqldatas) > 0 BEGIN SET @sqldatas = @sqldatas + ',' END; SET @sqldatas = @sqldatas + '[' + @targetKyoku + ']' SET @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'; FETCH next FROM cur INTO @targetKyoku; END; CLOSE cur; DEALLOCATE cur; SET @sqlstr = @sqlstr + ' FROM (select _15 as tuki, _1 FROM UsageLog_past12Ms) T PIVOT (Count([_1]) FOR [_1] IN '; SET @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'; EXECUTE (@sqlstr); END;">
GridVIewにどのように表示させたいかですが以下のような集計結果から1行目のINFを除いた分が表示されれば成功です。
tuki 01 1 2008 12:00AM 02 1 2008 12:00AM 03 1 2008 12:00AM 04 1 2008 12:00AM 05 1 2008 12:00AM INF 106 120 159 177 3 AD 411 170 100 196 9 AIWS 351 59 77 130 3 AIWS;SEI;STOR 3 0 0 0 0 APP 3527 4531 4370 3455 86 BAPP 366 80 185 127 2 BIP 82 7 40 37 0 せっかくなのでストアドプロシージャ化には挑戦しますが、このデータ構造と条件を前提にサポートいただければと思います。
よろしくお願いします。
-
例です。
CREATE PROCEDURE [test].[PivotTest]
(
@company nvarchar(10) = ''
)
as
begin
declare
@sqlstr nvarchar(max),
@sqldatas nvarchar(max),
@targetKyoku nvarchar(10)
declare cur cursor for
SELECT DISTINCT _15 FROM UsageLog_past12Ms where _15 is not null and company = case when @company != '' then @company else _15 end
set @sqldatas = ''
set @sqlstr = 'SELECT tuki'
open cur
if @@CURSOR_ROWS = 0
begin
close cur
deallocate cur
return
end
fetch next from cur into @targetKyoku
while (@@fetch_status <> -1 )
begin
if len(@sqldatas) > 0
begin
set @sqldatas = @sqldatas + ','
end
set @sqldatas = @sqldatas + '[' + @targetKyoku + ']'
set @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']'
fetch next from cur into @targetKyoku
end
close cur
deallocate cur
set @sqlstr = @sqlstr + ' FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms) T PIVOT (Count([_15]) FOR [_15] IN '
set @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'
exec sp_executesql @sqlstr, N'@company nvarchar(10)', @company
end
-
早速以下のようにシンプルなサンプルページを作って実行したところ会社をDropDownListから選んでもGridViewは何も表示されないという感じになりました。これから教えていただいたプロファイラーを使ってどういうやり取りが行われてそうなってしまうのかを調べようと思います。
よろしくお願いします。
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default3.aspx.vb" Inherits="Default3" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>無題のページ</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<aspropDownList ID="DropDownList1" runat="server"
DataSourceID="SqlDataSource1" DataTextField="Company"
DataValueField="Company" AutoPostBack="True">
</aspropDownList>
<aspqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:usagelogConnectionString14 %>"
SelectCommand="SELECT DISTINCT [Company] FROM [UsageLog_past12Ms]">
</aspqlDataSource>
</div>
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource2">
</asp:GridView>
<aspqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:usagelogConnectionString14 %>"
SelectCommand="PivotTest" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="company"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</aspqlDataSource>
</form>
</body>
</html> -
プロファイラを実行してトレースしたところおおよそ以下のようになりました。
どうやらSQLの実行は問題なくできていてそれ以外に原因があってひょうじされないということでしょうか?
よろしくお願いします。
exec PivotTest @company=N'DropDownListで選んだ実際の会社名'
DECLARE cur CURSOR FOR SELECT DISTINCT _15 FROM UsageLog_past12Ms WHERE _15 IS NOT NULL AND
company = CASE WHEN @company != '' THEN @company ELSE _15 ENDSET @sqldatas = ''
SET @sqlstr = 'SELECT tuki'
OPEN cur
1.2122
1.2122
1.4194
↓約300行ほど
1.6910
IF @@CURSOR_ROWS = 0
CLOSE cur
DEALLOCATE cur
RETURN
-
tosaito さんからの引用 これをテストで作ったDropDownListとストアドプロシージャとリンクしたGridViewで試したところこちらもちゃんと表示され、
しかも会社を選ぶと集計結果がすぐに正しく表示されるようになりました!
既に解決済みのようで、今頃になってレスするのもなんですが、一つ気になることがあり
ましたのでレスさせていただきました。
DropDownList で会社(テーブル上は Company ですよね?)を選んで GridView に
その会社のみのデータを抽出して表示させるということと思いますが、その場合もう一
箇所で抽出条件が必要ではないでしょうか?
つまり、
FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms)
というところで、
FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms WHERE Company=@Company)
とするとか。
間違っていたら失礼しました。
-
SurferOnWww さんからの引用
FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms)というところで、
FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms WHERE Company=@Company)
とするとか。
するどいですね。本当はtosaitoさんから言われるかな?と思っていたのですが、言われなかったのでこのままの仕様で良いのか、もしくはtosaitoさんの方で修正されたのかと思っています。結論から言えば
FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms)
でもかまいません。この場合、他のcompanyのデータが存在する月で、かつ、当該companyのデータが無い月は0と表示されます。
SurferOnWwwさんが指摘されたように、
FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms WHERE Company=@Company)
にすると、当該companyのデータが存在する月のみが表示されます。
後者の方が一般的に素直だと思いますが、仕様がはっきりわからなかったため、SQL文がより簡単な方をとりあえず載せました。
ちなみに、私が提示したストアドプロシージャに合わせるのであれば、以下のようになります。
FROM (select datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms WHERE Company = case when @Company != '''' then @Company else Company end