none
列・行ともに動的なマトリックスレポートについて RRS feed

  • 質問

  •  

    お世話になります。

    GridViewを使ってExcelでいうところのピボットテーブルのような統計レポートのようなものを作りたいのですがSQL文の書き方がぜんぜんわかりません。どなたか教えていただけますか?

    たとえばくだものが何時何個売れたかという販売データを例にすると、列はselect distinct くだもの from 販売データで、

    行はselect distinct 販売月 from 販売データになり、値が月別の販売個数になるようにしたいのです。

     

     みかん りんご いちご

    1月      4          31         16

    2月      3          23         61

    3月     45         23         36

    4月     23         32         63

    2008年6月9日 1:34

回答

すべての返信

  • SQL Server 2005でしょうか? であれば、夏椰さんの記事が参考になりそうです。

     

    PIVOTで遊ぶ
    http://blogs.wankuma.com/kaya/archive/2006/09/24/39459.aspx

    2008年6月9日 1:40
    モデレータ
  • ありがとうございます!まさにこれです。早速自分の実際のデータに置き換えて自分なりに書き換えてみたのですが、うまくいきません。どこが悪いかアドバイスいただけますでしょうか?エラーはデータ型nvarcharをdatetimeに変換中にエラーが発生しました。PIVOT演算子に不適切な値"3"が指定されました。とでます。

     

    実際のデータはこんな感じで。。。

     

    テーブル名:UsageLog_past12Ms

    _1               _15   

    2006/2/3       NHK

    2005/4/7       TBS

     

    これをこうしたいのです。

     

                          NHK        TBS

    2006/2                3           2

    2006/3                6           3

     

    よろしくお願いします。

     

     

    begin
            declare
            @sqlstr varchar(max),
            @sqldatas varchar(max),
            @targetMonth int;

        declare cur cursor for
            SELECT DISTINCT datepart(mm, _1) as  year FROM UsageLog_past12Ms ;

        set @sqldatas = '';
        set @sqlstr = 'SELECT _15';

        open cur ;
        fetch next from cur into @targetMonth ;
        while (@@fetch_status <> -1 )
        begin
            /*  */
            if len(@sqldatas) > 0
            begin
                set @sqldatas = @sqldatas + ','
            end ;
           
          
            set @sqldatas = @sqldatas + '[' + cast(@targetMonth as varchar) + ']'
          
            set @sqlstr = @sqlstr + ',[' + cast(@targetMonth as varchar(max)) + '] as y' + cast(@targetMonth as varchar(max)) ;
            fetch next from cur into @targetMonth ;
        end ;

        close cur ;
        deallocate cur ;   

        set @sqlstr = @sqlstr + ' FROM (SELECT [_15],[_1] FROM UsageLog_past12Ms) T PIVOT (Count([_1]) FOR [_1] IN ';
        set @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE' ;

     

       
        execute (@sqlstr) ;

    end ;

     

    2008年6月9日 6:14
  • ちょっと考え方が違います。以下のような感じです。

     

    begin
            declare
            @sqlstr varchar(max),
            @sqldatas varchar(max),
            @targetKyoku varchar(10);

        declare cur cursor for
            SELECT DISTINCT _15 FROM UsageLog_past12Ms ;

        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 datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms) T PIVOT (Count([_15]) FOR [_15] IN ';
        set @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE' ;

     

       
        execute (@sqlstr) ;

    end ;

    2008年6月9日 16:26
    モデレータ
  • ありがとうございます!

    早速実行してみたのですが、クエリは正常に実行されました。となりエラーは起こらないのですが、

    結果がまったく表示されません。データ自体が問題ではないようです。何が問題なのか教えていただけますでしょうか?

     

    よろしくお願いします。

     

     

     

    BEGIN DECLARE @sqlstr varchar(max), @sqldatas varchar(max), @targetKyoku varchar(max); DECLARE cur CURSOR FOR SELECT DISTINCT _15
                                                                                                                                                                                                                                                                                         FROM                     UsageLog_past12Ms;
    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 datepart(mm, _1) as tuki, _15 FROM UsageLog_past12Ms) T PIVOT (Count([_15]) FOR [_15] IN ';
    SET                          @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE'; EXECUTE (@sqlstr); END;

    2008年6月10日 1:31
  •  tosaito さんからの引用

    早速実行してみたのですが、クエリは正常に実行されました。となりエラーは起こらないのですが、

    結果がまったく表示されません。データ自体が問題ではないようです。何が問題なのか教えていただけますでしょうか?

     

    こちらでは正常に表示されます。結果がまったく表示されないということですが、0件ということでしょうか? それとも結果を表示するタブやペインなどが表示されないということでしょうか?

     

    テストデータ

     

    テーブル:UsageLog_past12Ms

     

    _1                              _15
    -------------------------------------------
    2008/06/01 0:00:00          TBS
    2008/06/02 0:00:00          TBS
    2008/06/03 0:00:00          NHK       
    2008/07/01 0:00:00          TBS
    2008/07/01 0:00:00          NHK       
    2008/07/05 0:00:00          NHK 

    2008年6月10日 4:15
    モデレータ
  • たびたびありがとうございます。

    確かに新しくこのサンプルとまったく同じテーブルを作成して実行してみたところ表示されました。

    Management Studio Express のSQLペインに貼り付けて実行しているのですが実際のデータを使用すると結果(R)がブランクで正常終了します。いろいろと試したところ以下のサンプルのテーブル1行目のように_15がNullになっているレコードが含まれると結果が表示されず正常終了しましたというメッセージがでる現象が発生することがわかりました。Nullが入っていても集計結果が表示されるようにするにはどうすればよいのでしょうか?教えてください。

     

    よろしくお願いします。

     

     

    _1                              _15
    -------------------------------------------
    2008/07/05 0:00:00          Null

    2008/06/01 0:00:00          TBS
    2008/06/02 0:00:00          TBS
    2008/06/03 0:00:00          NHK       
    2008/07/01 0:00:00          TBS
    2008/07/01 0:00:00          NHK       
    2008/07/05 0:00:00          NHK 

     

     

     

    よろしくお願いします。

    2008年6月10日 5:12
  • SELECT DISTINCT _15 FROM UsageLog_past12Ms where _15 is not null

     

    として下さい。

    2008年6月10日 7:59
    モデレータ
  • ありがとうございます。その点はご指摘のとおりNullを除外することでうまくできたのですが、

    実際のデータには以下の例のように文字間にスペースが入っているデータがあり、それが原因で失敗してしまうことがわかりました。この場合は、”ME付近に不適切な構文があります。T付近に不適切な構文があります。”とエラーになってしまいます。

    スペースがあってもエラーにならないようにするにはどうすればよいのでしょうか?

    ご教授ください。

     

    よろしくお願いします。

     

    _1                 _15

    2007/01/01     NTT ME
    2008年6月11日 1:31
  • [ ]でくくる意味を調べてみて下さい。

     

    set @sqlstr = @sqlstr + ',[' + @targetKyoku + '] as [' + @targetKyoku + ']';

    2008年6月11日 2:32
    モデレータ
  • ありがとうございました。うまくいきました。

    非常に助かりました。

     

    今後ともよろしくお願いします。

    2008年6月11日 3:09