縦持ちレコードでレコード追加時にSQL修正することなく可変横持化したい。

Question

前提・実現したいこと

縦持ちのテーブルを横展開するSQL文を書きましたが
今のままでも動作はするのですが
レコードが追加された際にいちいちSQLの修正が発生するので
これを避けたいと考えております。

該当のソースコード

SELECT
 m_search.code,
 Max(IIf([m_search.panel_no]="1",[m_search.search_flg])) AS 1,
 Max(IIf([m_search.panel_no]="2",[m_search.search_flg])) AS 2,
 Max(IIf([m_search.panel_no]="3",[m_search.search_flg])) AS 3,
 Max(IIf([m_search.panel_no]="4",[m_search.search_flg])) AS 4,
 Max(IIf([m_search.panel_no]="5",[m_search.search_flg])) AS 5,
 Max(IIf([m_search.panel_no]="6",[m_search.search_flg])) AS 6,
 Max(IIf([m_search.panel_no]="7",[m_search.search_flg])) AS 7,
 Max(IIf([m_search.panel_no]="8",[m_search.search_flg])) AS 8,
 Max(IIf([m_search.panel_no]="9",[m_search.search_flg])) AS 9,
 Max(IIf([m_search.panel_no]="10",[m_search.search_flg])) AS 10,
 Max(IIf([m_search.panel_no]="11",[m_search.search_flg])) AS 11,
 Max(IIf([m_search.panel_no]="12",[m_search.search_flg])) AS 12,
 Max(IIf([m_search.panel_no]="13",[m_search.search_flg])) AS 13,
 Max(IIf([m_search.panel_no]="14",[m_search.search_flg])) AS 14
FROM m_search.panel_no
GROUP BY m_search.code
ORDER BY m_search.code;

試したこと

VBAとループを使ってSQLを動的に生成すれば実現できそうだとは思うのですが、
ゆくゆくSQlite等に移行することも考えているので、できればそれを踏まえて
SQL文のみで実現できないかと考えております。
色々とGoogleで調べてみたりしたのですがなかなかこれだという
情報に巡り合えませんでした。
どうしてもプログラムのみしか解決策がないのであれば仕方ないのですが
SQLのみで解決できる手段があれば知りたいです。
すいませんがお力をお貸しください。

補足情報（FW/ツールのバージョンなど）

MS-ACCESS2016
Windows10Pro 64bit
ここにより詳細な情報を記載してください。

Answer 1

SQLiteと共通のSQLで実現するというのは厳しいかと思います。
ですので、クロス集計にしろ、現在の形にしろ、メンテナンスフリーにするのは、動的にSQLを生成することで、良いのではないでしょうか。
どのみち、SQLを実行するところからは同じなんですし。

もう一つは、そのクロス集計の結果を必要としている機能を見直すという方法もありますね。

追記

以下の自作関数を作成するなら、SQLはメンテナンスフリーにできるかと思います。
DJoin 関数 - リスト文字列を取得する方法

尚、取り出しの際に配列として処理する必要もありですが。

Comments

[Otazoman]

クロス集計部分については横展開しないとフラグの設定がうまく動作しない関係もあってどうしても横展開せざるをえない箇所です。
メンテナンスフリーとするとなるとフロント側でパラメータ長を取得して
そのパラメータ長を元に動的にCASE文を必要数展開するというやり方が
簡単かもしれないですね。

Answer 2

見た感じクロス集計クエリでよさそうです。
行見出しにcode
列見出しにpanel_no
値にsearch_flg

でいいかと思います。データに存在しない[panel_no]があると歯抜けになります。
歯抜けにしたくない場合強制的に表示させるためのオプションで「クエリ列見出し」があるので
そこを埋めると表示できるようになります。

#追記
あとでビット演算するとしたらこんなかんじですかね。flgが1か0だと楽なんですが…

SELECT
 m_search.code,
 sum(2^(clng([m_search.panel_no])-1)) AS panel_no_sum
FROM m_search.panel_no
WHERE m_search.search_flg=1
GROUP BY m_search.code
ORDER BY m_search.code;

Comments

[Otazoman]

早速のご回答ありがとうございます。
実はクロス集計クエリで生成したものをSQLに焼き直そうとしているところです。

そのクロス集計クエリに別のクエリをつなげていて、それをベースにSQLを
準備してそのSQLをVBA以外の別言語で使用する予定です。
それでSQLに置き換えた際に動的にレコード追加した際にメンテナンスレスな
SQLの書き方がないかなぁと思って質問した次第です。
IIFとCASEを使った書き方だとメンテナンスが発生するのでそのメンテナンスを
回避したいと思っています。

[sousuke]

ピボットはSQL Serverならわりかしいけるんですが、SQLiteになるとわからないですね…。

別のアプローチとしては考えられるのは
データはフラグのようなので１列にすべてをふくめてビット演算する
ということでしょうか。

フラグが立っているもの中で２のpanel_noでべき乗して２進数化することで
そのsumをビット演算で取り出して使うというやり方です。
どうやって使っているのかわからないのであれなんですが…

1,0,1,1,0→13(２進数表記で10110)
こんな感じなイメージです。

これで扱うとSQLは一通りで済んでフロントのプログラムだけごちゃごちゃ
すればいいことになります。

[Otazoman]

ご回答ありがとうございます。意図されているところ理解できました。
確かにpanel_no毎に含まれているsearch_flgの値を1行にして
出力するとすれば、SQLの変更はいらなくなりますね。
search_flgですがNullと1と2というのが現在のパラメータです。
そのため1010のようにきれいに並んでおらず意図する値が
取り出せないかもしれません。
※実際に試すと値はうまく取れませんでした。

色々とありがとうございました。

[sousuke]

そこはNULL、１、２を列にすればいいと思いますよ。
SELECT m_search.code,
sum(iif([search_flg]=1,2,0)^(clng([m_search.panel_no])-1)) AS panel_no1_sum,
sum(iif([search_flg]=2,2,0)^(clng([m_search.panel_no])-1)) AS panel_no2_sum,
sum(iif(isnull([search_flg]),2,0)^(clng([m_search.panel_no])-1)) AS panel_noNULL_sum
FROM m_search.panel_no
GROUP BY m_search.code
ORDER BY m_search.code;

これで
panel_no1_sumにflg=1
panel_no2_sumにflg=2
panel_noNULL_sumにflg is NULL
のビット値がはいるのでそれぞれandで取り出せば一応取れます。
flgの数が増えるとSQLに変更が出ますが、flgの数が増えることは
よっぽどだと思います。

[Otazoman]

ありがとうございます。
早速試してみます。

Answer 3

結局、以下の様な形でしのぎました。

	var getPanel = 'SELECT m_search.panel_no '
		+ 'FROM m_search '
		+ 'GROUP BY m_search.panel_no '
		+ 'ORDER BY m_search.panel_no;'
	var panNoJson = getJsonData(getPanel);
	//横展開の表を作成する
    var sPivotSql =	'SELECT m_search.code, ' 
	var rt = JSON.parse(panNoJson);
	for(var i=0;i < rt['data'].length;i++){
		var targetNo = rt['data'][i].panel_no;
		// MS-ACCESS用
		sPivotSql = sPivotSql + ' Max(IIf([m_search.panel_no]=' + targetNo
					 +',[m_search.search_flg])) AS' + targetNo + ',' ;
		// SQLite用
		sPivotSql = sPivotSql + ' Max(case when panel_no=' + targetNo
					 + 'then search_flg else 0 end ) AS \'' + targetNo + '\''; 
		if(i !== rt['data'].length - 1 ){
			sPivotSql = sPivotSql + ',';
		}
	}
	sPivotSql = sPivotSql + ' FROM m_search '
				+ 'GROUP BY m_search.code '
				+ 'ORDER BY m_search.code;'
	console.log(sPivotSql);

色々とヒントをいただき
ありがとうございました。