previous arrowprevious arrow
next arrownext arrow
Shadow
Slider
SQL

SQL ヘッダー行と明細(データ)行で項目数が異なるデータを抽出する

IFファイルを作るときに、ヘッダー行、明細行、明細行…、ヘッダー行、明細行、明細行…のような作りのことがあります。

こんなファイルですね。

ヘッダ 項目A 項目B
明細 項目C 項目D
明細 項目C 項目D
ヘッダ 項目A 項目B
明細 項目C 項目D
明細 項目C 項目D

それをSQLで頑張って出せるようにしてみました。

備忘録を兼ねて紹介します。

デモンストレーションで動かした動画を載せておきます。

サンプルの発注・発注明細データ

扱うテーブルは以下のようなデータです。

発注テーブル

列名(英語) 列名(日本語) 備考
BUYING_ID 発注ID PK
DENPYO_NO 伝票NO
HACHU_DATE 発注日
MARCHANT_CD 仕入先CD
BUYING_COUNT 発注合計数
ARRIVAL_LOCATION_CD 入荷場所CD
SHIPMENT_LOCATION_CD 出荷場所CD
SHIPPING_COMPANY_CD 配送会社CD
REGISTER_USERCD 登録ユーザCD

発注明細テーブル

列名(英語) 列名(日本語) 備考
BUYING_ID 発注ID PK
BUYING_DETAIL_ID 発注明細ID PK
DENPYO_NO 伝票NO
DENPYO_ROW_NO 伝票行NO
SKU SKU
PURCHASE_PRICE 仕入価格
BUYING_DEATIL_COUNT 発注明細数

サンプルSQL

サンプルのSQLを見ていきましょう。

SQLの解説は下の方でします。

テストデータを作るのが手間だと思います。

なので、テーブル作成からデータ登録までできるSQLを準備しました。

こちらからダウンロード可能です。

抽出結果

ちょっと見づらいですが、ヘッダー行と明細行で順番に抽出できています。

ヘッダーと明細行を1SQLにする考え方

どんなふうにヘッダー行と明細行を出していくかを書いておきます。

考え方としては以下の2ステップです。

①列数が違うテーブルをとりあえず一つのテーブルにする

②順番をヘッダー、明細、項目でソート

①列数が違うテーブルを一つのテーブルにする

そもそも列数が違うテーブルを1つの抽出結果にすることは難しいです。

なので、ひとまず、以下のように1つの抽出結果にします。

②順番をヘッダー、明細、項目でソート

一つにできれば、何となく形が見えてきますよね。

後は順番をヘッダー、明細、ヘッダー、明細と並べ替えるだけです。

並べ替えるとこんな感じになります。

SQLの解説

考え方の①と②に当てはめて解説していきます。

①列数が違うテーブルを一つのテーブルにする

18行目~49行目で発注と発注明細のテーブルを1つの抽出結果にしています。

UNIONでヘッダと明細を合せる

UNIONを使って1つの抽出結果にします。

ただし、発注明細の方が列数が少ないので、NULLを取得して列数を合せて抽出をしています。

UNION後の列の型を変換

そのままでは、ヘッダと明細で列の型が異なる値が入るため、エラーが出ます。

例えば、8列目です。

ヘッダーのARRIVAL_LOCATION_NAMEは文字型

明細のBUYING_DEATIL_COUNTは数値型

UNIONするとエラーになります。

そのため、CONVERT(VARCHAR(50),列名)を使って、型を合せるようにします。

ヘッダと明細区分を付ける

発注はヘッダ、発注明細は明細行なので、抽出するときに01と02で区分を付与しています。

この次にORDER BYでソートするために使います。

②順番をヘッダー、明細、項目でソート

17行目~49行目で元データは作成できました。

なので、そのテーブルをAとして抽出をしています。

この時、データの順番はそのままではだめなので、

[ヘッダ/明細]、[発注ID]、[発注明細ID]の順にORDER BYでソートしています。

ソート時はUNIONするテーブルの二つの同じ項目(列)がないとダメです。

COL1の18行目と36行目は同じ項目(列)の「ヘッダ/明細区分」です。

COL2の19行目と37行目も同じ項目(列)の「発注ID」です。

例えば、仮にCOL2が「発注ID」と「伝票番号」だとソートしたら、数値が若い順に並んでしまいます。

 

IFファイルとして出力する方法も書いているので良ければみて下さい。

SQL BCPコマンド ストアド/SQLを使ってTSV・CSVを出力 SQLでインターフェースファイルを作るときに、ファイルを出力する必要があります。 Transact-SQLを組んでもいいの...
関連している記事

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です