複数列の横持ちデータへの変換について

Question

前提・実現したいこと

注文親テーブルと注文子テーブル(色別に色番号と色名と色数)があり、
注文親テーブルの注文IDごとに、注文子テーブルの各項目を横にならべるようにして、
ADOでレコードセットを取得してユーザフォームのリストボックスに表示させたいです。
色は最大7個あります。

発生している問題・エラーメッセージ

子テーブルの本数だけなら、SQLでできましたが、色番・色名も横持ちにしたい場合どうすればいいかわかりません。

該当のソースコード

CREATE TABLE 注文親テーブル(注文ID NUMBER,品名 CHAR);

CREATE TABLE 注文子テーブル(
注文子ID NUMBER,
注文ID NUMBER,
注文番号子 NUMBER,
色番 CHAR,
色名 CHAR,
色本数 NUMBER
);


INSERT INTO 注文親テーブル (注文ID,品名) VALUES (1,'あああ');
INSERT INTO 注文親テーブル (注文ID,品名) VALUES (2,'いいい');
INSERT INTO 注文親テーブル (注文ID,品名) VALUES (3,'ううう');



INSERT INTO 注文子テーブル (注文子ID,注文ID,注文番号子,色番,色名,色本数) VALUES (101,1,1,'BK','ブラック',20);
INSERT INTO 注文子テーブル (注文子ID,注文ID,注文番号子,色番,色名,色本数) VALUES (102,1,2,'GY','グレー',30);
INSERT INTO 注文子テーブル (注文子ID,注文ID,注文番号子,色番,色名,色本数) VALUES (201,2,1,'BR','ブラウン',40);
INSERT INTO 注文子テーブル (注文子ID,注文ID,注文番号子,色番,色名,色本数) VALUES (202,2,2,'Ｇ','ゴールド',6);
INSERT INTO 注文子テーブル (注文子ID,注文ID,注文番号子,色番,色名,色本数) VALUES (203,2,3,'BL','ブルー',100);
INSERT INTO 注文子テーブル (注文子ID,注文ID,注文番号子,色番,色名,色本数) VALUES (301,3,1,'ＣＡ','キャメル',5);
INSERT INTO 注文子テーブル (注文子ID,注文ID,注文番号子,色番,色名,色本数) VALUES (302,3,2,'BL','ブルー',100);

色本数のみ横持ちのSQL

TRANSFORM sum(色本数) 
SELECT 注文ID,sum(色本数)
FROM 注文子テーブル
GROUP BY 注文ID
PIVOT 注文番号子

試したこと

他のデータベースだとwhen句を使っていそうだとわかりましたが、VBAだと使用できません。
SQLだけで解決できるのでしょうか？
その場合　SELECT 注文ID,色番,色名,色本数 FROM　[注文子テーブル$] WHERE 注文番号子 = 1’各色ごと
で各色ごとにクエリを作成してjoinさせる方法でしょうか？
ただ7色あるので、大変だなと思っており、ほかの方法があれば教えていただきたいです。
どうぞよろしくお願い致します。

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

データもエクセルで保存して、エクセルでSQLを実行しています。
バージョンは2019です。

Comments

[Orlofsky]

ここの掲示板ではMarkdownの使い方を覚えてください。
テーブル定義はCREATE TABLE文で、テーブル中のデータはINSERT文で https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正できるとSQLの動作確認が容易になるので、適切なコメントが付き易くなります。

[Orlofsky]

同じSQLでもデータベースやそのバージョンによって方言が大きいですから、どのデータベースを使うのかを質問のタグで示したり、質問にバージョンも明記した方が適切なコメントが付き易いです。

[bors]

エクセルのシートのデータをVBAでSQL文を実行して集計しています。そのため、データベースは使用していません。
エクセルだと、CREATEができないため、一番近いaccessを見本としてCREATE文を書きました。
エラーになったらすいません。ご回答いただいたリンク明日参考にさせていただきます。
すべてエクセルのため、標準的な構文しか使えないのがネックにならないといいのですが。
特殊な環境で申し訳ありません。

[Orlofsky]

データ量が多くなったり、複数のユーザーが同じシートを更新しないといけなくなったりする前にMySQLかMariaDB辺りに移行しては？最近はかなりの機能を無料で使えます。SQLiteは非力過ぎ。
https://excel-ubara.com/vba_sql/

[bors]

サイト拝見したのですがどれが使えそうかわからなったので、とりあえずサブクエリで書きました。
せっかく載せていただいたのに申し訳ありません。
リンクのエクセルの神髄のSQLはすべて勉強しましたが(だいぶ忘れてますが)、遠隔地とのやり取りがありデータベースの置き場所？がよくわかっていないので、データベースについて勉強してみます。
とりあえずはエクセルファイルを開いた人しか実行できないようにして凌ぐつもりでいます。
お付き合いいただきありがとうございました。

Answer 1

SQL Server 縦に並んだデータを横にカンマ区切りの列データで取得する方法
Oracle LISTAGG
MySQL GROUP_CONCAT
PostgreSQL STRING_AGG
BigQuery ARRAY_AGGやARRAY_TO_STRING

Answer 2

Dim sSql As String
sSql = ""
sSql = sSql & "SELECT"
sSql = sSql & " TOYADATE.注文ID, TOYADATE.品名, "
sSql = sSql & "TKODATE.色番1,TKODATE.色名1,TKODATE.色本数1,TKODATE.色番2,TKODATE.色名2,TKODATE.色本数2,"
sSql = sSql & "TKODATE.色番3,TKODATE.色名3,TKODATE.色本数3,TKODATE.色番4,TKODATE.色名4,TKODATE.色本数4,"
sSql = sSql & "TKODATE.色番5,TKODATE.色名5,TKODATE.色本数5,TKODATE.色番6,TKODATE.色名6,TKODATE.色本数6,"
sSql = sSql & "TKODATE.色番7,TKODATE.色名7,TKODATE.色本数7"
'親テーブル TOYADATE
sSql = sSql & " FROM (SELECT 注文ID,品名 FROM [T注文親$] WHERE FLG削除 = 0) TOYADATE"
sSql = sSql & " LEFT OUTER JOIN"
'子テーブル横持ち TKODATE　(SELECT ～)TKODATEまでで１括弧
sSql = sSql & " (SELECT"
sSql = sSql & " TDATE.注文ID,"
sSql = sSql & "TCOL1.色番 AS 色番1,TCOL1.色名 AS 色名1,TCOL1.色本数 AS 色本数1,TCOL2.色番 AS 色番2,TCOL2.色名 AS 色名2,TCOL2.色本数 AS 色本数2,"
sSql = sSql & "TCOL3.色番 AS 色番3,TCOL3.色名 AS 色名3,TCOL3.色本数 AS 色本数3,TCOL4.色番 AS 色番4,TCOL4.色名 AS 色名4,TCOL4.色本数 AS 色本数4,"
sSql = sSql & "TCOL5.色番 AS 色番5,TCOL5.色名 AS 色名5,TCOL5.色本数 AS 色本数5,TCOL6.色番 AS 色番6,TCOL6.色名 AS 色名6,TCOL6.色本数 AS 色本数6,"
sSql = sSql & "TCOL7.色番 AS 色番7,TCOL7.色名 AS 色名7,TCOL7.色本数 AS 色本数7"
sSql = sSql & " FROM"
'’子テーブルの注文ID
sSql = sSql & " (((((((SELECT 注文ID FROM [T注文子$] WHERE FLG削除 = 0 GROUP BY 注文ID) TDATE"
'’子テーブルの各色
sSql = sSql & " LEFT OUTER JOIN (SELECT 注文ID,色番,色名,色本数 FROM [T注文子$] WHERE FLG削除 = 0 AND 注文番号子 = 1) TCOL1 ON TDATE.注文ID = TCOL1.注文ID)"
sSql = sSql & " LEFT OUTER JOIN (SELECT 注文ID,色番,色名,色本数 FROM [T注文子$] WHERE FLG削除 = 0 AND 注文番号子 = 2) TCOL2 ON TDATE.注文ID = TCOL2.注文ID)"
sSql = sSql & " LEFT OUTER JOIN (SELECT 注文ID,色番,色名,色本数 FROM [T注文子$] WHERE FLG削除 = 0 AND 注文番号子 = 3) TCOL3 ON TDATE.注文ID = TCOL3.注文ID)"
sSql = sSql & " LEFT OUTER JOIN (SELECT 注文ID,色番,色名,色本数 FROM [T注文子$] WHERE FLG削除 = 0 AND 注文番号子 = 4) TCOL4 ON TDATE.注文ID = TCOL4.注文ID)"
sSql = sSql & " LEFT OUTER JOIN (SELECT 注文ID,色番,色名,色本数 FROM [T注文子$] WHERE FLG削除 = 0 AND 注文番号子 = 5) TCOL5 ON TDATE.注文ID = TCOL5.注文ID)"
sSql = sSql & " LEFT OUTER JOIN (SELECT 注文ID,色番,色名,色本数 FROM [T注文子$] WHERE FLG削除 = 0 AND 注文番号子 = 6) TCOL6 ON TDATE.注文ID = TCOL6.注文ID)"
sSql = sSql & " LEFT OUTER JOIN (SELECT 注文ID,色番,色名,色本数 FROM [T注文子$] WHERE FLG削除 = 0 AND 注文番号子 = 7) TCOL7 ON TDATE.注文ID = TCOL7.注文ID) TKODATE"
sSql = sSql & " ON TOYADATE.注文ID = TKODATE.注文ID"

参考にしたサイト
サブクエリを使ったSQL

• リスト上記サイトはWITH句の説明のサイトだが、エクセルではWITH句が使用できないため、サブクエリのみで記述した。
• テーブルが[~$]でなのはエクセルでシート名をデータとしてみなす時の記述法のため。

Worksheets("注文親")に注文親テーブルがあるので[T注文親$] で指定している
LEFTJOINが括弧で括られているのは、accessの仕様と同じく、JOINが複数の場合括弧で括らないとエラーになるため

• 上記サイトでWHEN句での記述があるがエクセル及びアクセスでは使用できない

　アクセスだとSWICHでできるのか？参考サイトAccess SQL マトリックスを作成する
ただエクセルで動くかは不明。エクセル2016くらいから新しくSWICH関数が追加されているが、うごくかは不明
ただ参考サイトのNZ関数がエクセルだと使用できないので、代わりにIIFを使用しないとダメなのは確か
NZをIIFに書き換え