みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

SQLServerのPIVOT絡みの処理

お世話になります。

従来はVB6のプログラム中で、従来ACCESSMDBからデータを取得していました。
これをDBを変更し、データ自体はOracle、接続先はSQLServer(2005)との仕組みに変更することとなりました。
(SQLServerのリンクサーバ機能にてOracleからデータを取得する)

ここで、元のアクセスにて行っていたクロス集計を移行するため
次のようなSQL文(メイン部位以外は端折ってます)を書いたのですが、
「オペランドのデータ型 nvarchar は sum 演算子では無効です」
との警告が出てしまいます。
(”SUM(oraData.suuryou)” を、”Count(oraData.suuryou)”とした場合はデータ取得できました。)

原因、及び回避策についてお知恵を拝借できませんでしょうか?
(PIVOT相当の処理をプログラム側で行うことで回避できますが、できればSQLで終えてしまいたいです)

SELECT
    pvt.moji
   ,pvt.moji2
   ,SUM([01]), SUM([02]), SUM([03]), SUM([04]), SUM([05]), SUM([06]), SUM([07]), SUM([08]), SUM([09]), SUM([10]) 
   ,SUM([11]), SUM([12]), SUM([13]), SUM([14]), SUM([15]), SUM([16]), SUM([17]), SUM([18]), SUM([19]), SUM([20]) 
   ,SUM([21]), SUM([22]), SUM([23]), SUM([24]), SUM([25]), SUM([26]), SUM([27]), SUM([28]), SUM([29]), SUM([30]) 
   ,SUM([31]) 
From
   openquery  (LinkServerName, '
     SELECT
       moji
      ,moji2
      ,hiduke
      ,TO_CHAR(hiduke, ''dd'') AS dd
      ,hiduke
      ,suuryou
      ,Sum(suuryou) AS suuryou
     FROM
       table
     WHERE
       hiduke BETWEEN TO_DATE(''2008/04/01'', ''YYYY/MM/DD'') AND TO_DATE(''2008/04/30'', ''YYYY/MM/DD'') 
     UNION
     SELECT
       moji
      ,moji2
      ,hiduke
      ,TO_CHAR(hiduke, ''dd'') AS dd
      ,hiduke
      ,suuryou
      ,Sum(suuryou) AS suuryou
     FROM
       table
     WHERE
       hiduke BETWEEN TO_DATE(''2008/04/01'', ''YYYY/MM/DD'') AND TO_DATE(''2008/04/30'', ''YYYY/MM/DD'') 
   ') AS oraData
PIVOT
    ( 
       SUM(oraData.suuryou) 
       FOR oraData.dd IN
          (
             [01], [02], [03], [04], [05], [06], [07], [08], [09], [10] 
            ,[11], [12], [13], [14], [15], [16], [17], [18], [19], [20] 
            ,[21], [22], [23], [24], [25], [26], [27], [28], [29], [30] 
            ,[31] 
          ) 
    ) AS pvt
WHERE
   moji = "xxx" OR moji2 = "yyy"

投稿日時 - 2008-04-27 19:52:05

QNo.3980507

困ってます

質問者が選んだベストアンサー

自分が参考にしたサイトを記しておきます。
オチは「ストアドでPIVOTを含むSQLを生成し実行」です。

参考URL:http://blogs.wankuma.com/kaya/archive/2006/09/24/39459.aspx

投稿日時 - 2008-05-06 06:25:27

お礼

ご回答ありがとうございます。
該当ページ参考にさせていただきます。


本件に関して、直接の原因はエラーメッセージ通りsuuryou項目がnvarcharとなっていたためでした。(Oracle上では数値型だったのですが・・・)

どうも
1.SQLServerのリンクテーブル機能によるopenquery
2.Oracleとのリンクテーブル機能
3.openquery中での、UNION
この3つのいずれか、或いは複合により、取得データの型が全てnvarcharになっていたようです。

今回は、openqueryにて取得したデータに直接PIVOTを使用するのではなく、SQLServer側で一度suuryou項目を数値に変換したものに対してPIVOTを使用することで、目的の値を取得することができました。

(さんぷる)
SELECT
    pvt.moji
   ,pvt.moji2
   ,SUM([01]), SUM([02]) ,(略)
From
   (SELECT
     oraData.moji
    ,oraData.dd
    ,CAST(oraData.suuryou AS numeric(10,0)) AS suuryou
   FROM
     openquery  (略) AS oraData
   WHERE
     oraData.moji = "xxx" OR oraData.moji2 = "yyy"
   ) AS oraData2
PIVOT
    ( 
       SUM(oraData2.suuryou) 
       FOR oraData2.dd IN
          (
             [01], [02],(略)
          ) 
    ) AS pvt

結局、PIVOTとタイトルをつけるも、それ以外がネックとなっていたようです。
お手数をおかけいたしました。


※何かしら回答をいただけないと、経過を追記することも、
 閉じることもできないのは不便ですね。

投稿日時 - 2008-05-07 09:19:54

ANo.1

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(1)

あなたにオススメの質問