Google Spread Sheetのquery関数を利用してデータフレームを再編成したい

Question

実現したいこと

Google Spread Sheetのquery関数を利用してデータフレームを再編成したい
一行の中に複数の同レベルの情報が並列して登録されている時，それらを分割して再編成したいです．

対象データ

Google Formを利用してグループのリーダーがメンバー数名の情報を入力し，整形前図の形式でSpread Sheetに出力します．
Spread Sheetにはリーダーが登録した際のタイムスタンプが記録されており，最終的にはこれによってソートする予定です．

※ 疑似個人情報生成サービスで生成した架空の内容です

加工後の期待値

下記のような形式に整形したい．これを実現するためのクエリが分かりません．

要件

1. 横一列を複数カラムごとにセットで分割し，登録日を加えて保存 (氏名+年齢セットで左から右のイメージ)
2. 空欄の場合は前倒して整形 (一行目の氏名10,年齢10は空欄なので前倒し)
3. [可能なら] 横一列の登録順も保存したい．登録日を1秒ずつ増やすなどで順序をつける？

現段階で実現できていること

下記の式で下記のような抜き出し方には成功していますが，要件は何一つ満足しておらず，似て非なるものです．

=QUERY({B:C;D:E;F:G;H:I;J:K;L:M;N:O;P:Q;R:S;T:U},"SELECT *")

さいごに

最悪，Google ColabかGASでごり押し修正するのですが，折角こうしてSQLを学ぶタイミングがあるから向き合ってみようという次第です．
回答お待ちしております．

Comments

[YellowGreen]

クエリの数式をご希望のようですので、こちらは回答にはなりませんが、
GASでご希望の処理を行う場合の例を参考まで。

//フォーム送信のトリガーで起動したときは、
//フォームから送信された最終行のみを処理する
//手動で実行したときは全てのデータを処理する
function col2Row(e) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const frSheet = ss.getSheetByName('フォームの回答 1');
const toSheet = ss.getSheetByName('シート2');
const values = e ?
// 1 件ごとにトリガー処理
// e.values ://送信データを直接処理するなら次の行をコメントアウトしてこの行を使う
frSheet.getRange(frSheet.getLastRow(), 1, 1, frSheet.getLastColumn()).getValues() :
//一括処理
frSheet.getDataRange().getValues();
if (!e) {
values.splice(0, 1);//一括処理のときは見出し行（1行）を除外
}
const data = [];
for (const value of values) {
const time = new Date(value.shift());//更新日時とデータに分離
while(value[0]) {//データがなくなるまで繰り返す
data.push([
//最初の要素はタイムスタンプ（書式を合わせるため文字列とする）
Utilities.formatDate(time, 'JST', 'yyyy/MM/dd HH:mm:ss'),
//次が名前と年齢のペア
...value.splice(0, 2),
]);
time.setSeconds(time.getSeconds() + 1);//時刻を1秒進める
}
}
if (data.length > 0) {
if (e) {
// 1 件ごとにトリガー処理（最終行に追記）
toSheet.getRange(toSheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
} else {
//一括処理（2行目以降に一括記入）
toSheet.getRange(2, 1, data.length, data[0].length).setValues(data);
}
}
}

Answer 1

query関数＋αで無理矢理成型した例です。

=SORT(WRAPROWS(FLATTEN(
   QUERY($A2:$U,"select A,B,C where B is not null or C is not null")
  ,QUERY($A2:$U,"select A,D,E where D is not null or E is not null")
  ,QUERY($A2:$U,"select A,F,G where F is not null or G is not null")
  ,QUERY($A2:$U,"select A,H,I where H is not null or I is not null")
  ,QUERY($A2:$U,"select A,J,K where J is not null or K is not null")
  ,QUERY($A2:$U,"select A,L,M where L is not null or M is not null")
  ,QUERY($A2:$U,"select A,N,O where N is not null or O is not null")
  ,QUERY($A2:$U,"select A,P,Q where P is not null or Q is not null")
  ,QUERY($A2:$U,"select A,R,S where R is not null or S is not null")
  ,QUERY($A2:$U,"select A,T,U where T is not null or U is not null")
), 3), 1, TRUE)

参照テーブル定義

• A～U列が入力テーブル
• 1行目はヘッダー
• 2行目以降にデータ

---

正直に言うとキモは FLATTEN → WRAPROWS で、QUERY関数で再生成したと言ってよいかどうか怪しい所……。あと割と重いです
GASで手続き的に処理した方が圧倒的に楽で素直に記述でき、特に重くなることも無いと思われます。

「最悪，Google ColabかGASでごり押し修正」とありますが
一般的には、データ構造に合わせたロジックを取捨選択できるのが妥当な判断で
今回のように向いてないロジックを決め打ちで採用することを「ごり押し」と呼ぶのではないかと……

Answer 2

要件１/2を満たすものです。（条件3は満たしていません）

---

たとえば、組み替えたいデータの範囲が「A列の1行目」から「U列まで」であると仮定します。
この場合は、そのデータ列と重ならない列（V列以降）に下記の数式を入れてください。
注意：この数式の結果が展開される範囲のセルに、この数式以外のデータが入っていてはいけません（展開先のセルにデータがあると、#REFエラーになります）

=ArrayFormula(QUERY({ A1:C; A1:A, C1:D; A1:A, F1:G; A1:A, H1:I; A1:A, J1:K; A1:A, L1:M; A1:A, N1:O; A1:A, P1:Q; A1:A, R1:S; A1:A, T1:U }, "WHERE (Col1 IS NOT NULL AND Col2 IS NOT NULL) OR (Col1 IS NOT NULL AND Col3 IS NOT NULL) ORDER BY Col1" ))

　

データが違う場所にあるときの例：
たとえばA列の「8行目」からデータがある場合は、

=ArrayFormula(QUERY({ A8:C; A8:A, C8:D; A8:A, F8:G; A8:A, H8:I; A8:A, J8:K; A8:A, L8:M; A8:A, N8:O; A8:A, P8:Q; A8:A, R8:S; A8:A, T8:U }, "WHERE (Col1 IS NOT NULL AND Col2 IS NOT NULL) OR (Col1 IS NOT NULL AND Col3 IS NOT NULL) ORDER BY Col1" ))

のようになります。

注意：

・データが多いと重くなります。
この手の「単なる表示の組み換え」は、GoogleスプレッドシートにおけるQuery関数の本来の使い方ではないと思います。

データが大量にあって重すぎて使いものにならないと感じられる場合は、素直にGASを使ってください。