SQL文でのFormat関数について

タグの編集
投稿者 河童  (社会人) 投稿日時 2011/6/8 15:43:41
こんにちは。
いつも大変お世話になっております。

SQL文中でのデータの整形について質問させてください。

以前に質問したときFormat関数は使用できないとわかったのですが、
今回もデータの整形で悩んでいます。

テーブル(T_請求)には
売上区分・・・int型
数量・・・bigint型
というフィールドがあります。

売上区分  数量
1             20

T_請求のデータをCSV出力するときには、
売上区分  数量
01            20.00
という形で出力したいです。

売上区分を2桁にする。
数量は少数点以下2桁を表示する(少数点以下は必ず00です)。

SQL文中でデータを整形するには
どうすれば良いでしょうか?

よろしくお願い致します。

'CSV出力SQL文作成
With strQRY
   .AppendLine("SELECT TS.売上日)
   .AppendLine("     , TS.売上区分CD AS 売上区分")
   .AppendLine("     , TS.数量")
   .AppendLine("     , '0.00' AS 試験数量")
(省略)
   .AppendLine(" FROM  M_得意先 MT")
   .AppendLine("INNER JOIN T_請求 TS")
   .AppendLine("   ON TS.請求先CD = MT.得意先CD")
   .AppendLine("LEFT JOIN M_商品 MS")
   .AppendLine("  ON MS.明細NO = TS.明細NO")
   .AppendLine(" AND MS.削除フラグ = 0")
   .AppendLine(" WHERE TS.区分 = 1")
   .AppendLine("   AND MT.削除フラグ = 0")
   .AppendLine("ORDER BY TS.伝票NO, TS.明細NO)
End With
投稿者 魔界の仮面弁士  (社会人) 投稿日時 2011/6/8 16:14:06
> SQL文中でのデータの整形について質問させてください。
SQL は、各社の「方言」があります。
使用しているデータベースは何ですか? また、そのバージョンは?

> Format関数
Format 関数を持つデータベースとしては、
MySQL や JET(Access)などがありますね。

> 売上区分・・・int型
> 数量・・・bigint型
int / bigint 型を持つデータベースとしては、
MySQL、SQL Server などがありますね。

> T_請求のデータをCSV出力するときには、
データベース側で書式を整えるのではなく、ファイルへの出力時に
VB 側で Format してやれば問題無いかと。

> 数量は少数点以下2桁を表示する(少数点以下は必ず00です)。
少数 → 小数

> SQL文中でデータを整形するには
> どうすれば良いでしょうか?
Oracle なら「SELECT TO_CHAR(123456, 'FM999990.00') FROM DUAL」で『123456.00』が得られます。
「SELECT TO_CHAR(123456, 'FM999,990.00') FROM DUAL」なら『123,456.00』です。
MySQL なら「select format(123456, 2)」で『123,456.00』が得られます。
SQL Server  なら「SELECT CONVERT(varchar, CONVERT(MONEY, 123456), 1)」で『123,456.00』です。
投稿者 shu  (社会人) 投稿日時 2011/6/8 16:54:49
CSV書き出し時のフォーマットを前提として

Dim A as integer
Dim B as integer

A= 1
B= 5000

A.ToString("00") => "01" という文字列になります。
B.ToString("0.00") => "5000.00"という文字列になります。

String.Format("{0:00}",A)
String.Format("{0:0.00}", B)
なども可
投稿者 河童  (社会人) 投稿日時 2011/6/8 17:06:09
こんにちは。
魔界の仮面弁士さん、お返事ありがとうございます。

すみません。
操作環境を書くのを忘れていました。

データベースは
SQLServer2005Express
を使用しています。

今までAccessしか使用してこなかったので、
VBでの操作に戸惑っています。

> 少数 → 小数
わぉ、はずかしい。
全然気付かずに変換していました。

>> SQL文中でデータを整形するには
>SQL Server  なら「SELECT CONVERT(varchar, CONVERT(MONEY, 123456), 1)」で『123,456.00』
ありがとうございます。
小数点を表示できました。
一度、Money型に変換するのが肝ですね。

もう一点教えて下さい。
売上区分のデータ整形ですが、
int型の1からテキスト型の01に変換する方法を教えて下さい。
SELECT CONVERT(varchar, TS.売上区分, '00')

int型の売上区分をCONVERT関数でテキスト型に変換するときの
変換スタイルで2桁にするにはどのように設定すればいいですか?

int型の売上区分は必ず1桁で
テキスト型に変換するときは頭に0を付けて2桁にしたいです。

よろしくお願い致します。


投稿者 河童  (社会人) 投稿日時 2011/6/8 17:48:44
売上区分のデータ整形ですが、
SELECT 
RIGHT('00' + CONVERT(varchar(2), TS.売上区分), 2)

テキスト型に変換して頭に'00'を接続して
RIGHT関数で2桁だけ取得することで出来ました。

shuさん、お返事ありがとうございました。
本来ならCSV書き出し時にフォーマットをする方法が正しいのでしょうが、
まだCSV書き出しのコードが未完成で試行錯誤しています。

横着してAccessで慣れているSQL文の方を改良したら良いのではと
思い、今回質問しました。

投稿者 shu  (社会人) 投稿日時 2011/6/9 08:18:11
あまり変わりませんが

Select RIGHT(CONVERT(varchar(3), TS.売上区分 + 100), 2)

こんな方法もあります。
CONVERTなしでも動作するかもしれません。