SEの転職活動で使うべきサイト・エージェント 目指せ年収アップ
SEの転職活動で使うべきサイト・エージェント 目指せ年収アップ
東南アジアをバックパッカーしながら転職活動をした記録
東南アジアをバックパッカーしながら転職活動をした記録
7年目で初の転職活動 2カ月で内定獲得した記録
7年目で初の転職活動 2カ月で内定獲得した記録
おすすめのプログラミングスクール めざせ就職・年収アップ!
おすすめのプログラミングスクール めざせ就職・年収アップ!
メンズクリア2年通い放題入会! その後のヒゲ状況(不定期更新)
メンズクリア2年通い放題入会! その後のヒゲ状況(不定期更新)
ブログ開始3年が経った月収が1万超えてた!
ブログ開始3年が経った月収が1万超えてた!
プログラミング独学・スクール・就職(目次)
プログラミング独学・スクール・就職(目次)
previous arrowprevious arrow
next arrownext arrow
 
Shadow
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を準備しました。

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

SELECT 
 A.COL1									'ヘッダ/明細区分'
,A.COL2									'発注ID/発注ID'
,A.COL3									'伝票番号/発注明細CD'
,A.COL4									'発注日/伝票番号'
,A.COL5									'仕入先CD/伝票行番号'
,A.COL6									'合計発注数/SKU'
,A.COL7									'入荷倉庫CD/仕入価格(税抜)'
,A.COL8									'入荷倉庫名称/発注明細数'
,A.COL9									'出荷倉庫CD/'
,A.COL10								'出荷倉庫名称/'
,A.COL11								'運送会社CD/'
,A.COL12								'運送会社名称/'
,A.COL13								'登録ユーザCD/'
FROM
(
	SELECT
	 '01'													AS	'COL1'
	,CONVERT(VARCHAR(50),BUYING.BUYING_ID)					AS	'COL2'
	,CONVERT(VARCHAR(50),BUYING.DENPYO_NO)					AS	'COL3'
	,CONVERT(VARCHAR(50),BUYING.HACHU_DATE)					AS	'COL4'
	,CONVERT(VARCHAR(50),BUYING.MARCHANT_CD)				AS	'COL5'
	,CONVERT(VARCHAR(50),BUYING.BUYING_COUNT)				AS	'COL6'
	,CONVERT(VARCHAR(50),ARRIVAL_LOCATION_CD)				AS	'COL7'
	,CONVERT(VARCHAR(50),ARRIVAL_LOCATION_NAME)				AS	'COL8'
	,CONVERT(VARCHAR(50),SHIPMENT_LOCATION_CD)				AS	'COL9'
	,CONVERT(VARCHAR(50),SHIPMENT_LOCATION_NAME)			AS	'COL10'
	,CONVERT(VARCHAR(50),SHIPPING_COMPANY_CD)				AS	'COL11'
	,CONVERT(VARCHAR(50),SHIPPING_COMPANY_NAME)				AS	'COL12'
	,CONVERT(VARCHAR(50),REGISTER_USERCD)					AS	'COL13'
	FROM BUYING
	
	UNION
	
	SELECT
	 '02'													AS	'COL1'
	,CONVERT(VARCHAR(50),BUYING_DETAIL.BUYING_ID)			AS	'COL2'
	,CONVERT(VARCHAR(50),BUYING_DETAIL.BUYING_DETAIL_ID)	AS	'COL3'
	,CONVERT(VARCHAR(50),BUYING_DETAIL.DENPYO_NO)			AS	'COL4'
	,CONVERT(VARCHAR(50),BUYING_DETAIL.DENPYO_ROW_NO)		AS	'COL5'
	,CONVERT(VARCHAR(50),BUYING_DETAIL.SKU)					AS	'COL6'
	,CONVERT(VARCHAR(50),BUYING_DETAIL.PURCHASE_PRICE)		AS	'COL7'
	,CONVERT(VARCHAR(50),BUYING_DETAIL.BUYING_DEATIL_COUNT)	AS	'COL8'
	,NULL													AS	'COL9'
	,NULL													AS	'COL10'
	,NULL													AS	'COL11'
	,NULL													AS	'COL12'
	,NULL													AS	'COL13'
	FROM BUYING_DETAIL
) A
ORDER BY 
	 A.COL2
	,A.COL1

抽出結果

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

ヘッダーと明細行を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を組んでもいいの...
ABOUT ME
LooseCarrot
LooseCarrot
ブログ運営をしているLooseCarrotです。 興味のあることにトライして発信していきます! プロフィール

関連している記事