google-sheets - 如何根据Google表格中的标志列获取垂直数据并水平提取数据？

Question

我在 Google 表格中有以下数据格式：

我想根据 Ready 标志是 Y 还是 N，将 Quantity 列提取为 2 个单独的列。 Item Name 和 Size 将是 group By 列，以及 Ready 标志。以下是查询运行后我想要的数据格式：

还没有尝试过任何解决方案，因为我已经离开编程循环很长时间了，而且对 SQL 超级生疏。

标签： google-sheetssubquerypivot-tableaggregation

Answer 1

={"Item name", "Size", "Not ready", "Ready";
 {QUERY(QUERY(QUERY(A2:D, 
  "select *", 0), 
  "select Col1, Col2, count(Col1) 
   where Col2 is not null 
   group by Col1, Col2 
   order by Col1 desc 
   label count(Col1)''", 0),
  "select Col1, Col2", 0),
  ARRAYFORMULA(ARRAY_CONSTRAIN(IFERROR(VLOOKUP(QUERY(QUERY(QUERY(A2:D, 
  "select *", 0), 
  "select Col1, Col2, count(Col1) 
   where Col2 is not null 
   group by Col1, Col2 
   order by Col1 desc 
   label count(Col1)''", 0),
  "select Col1", 0)&
  QUERY(QUERY(QUERY(A2:D, 
  "select *", 0), 
  "select Col1, Col2, count(Col1) 
   where Col2 is not null 
   group by Col1, Col2 
   order by Col1 desc 
   label count(Col1)''", 0),
  "select Col2", 0), 
  QUERY(QUERY({A2:A&B2:B, C2:D}, 
  "select *", 0), 
  "select Col1, Col2, Col3 
   where Col3='N'", 0), 2, 0), 0),
  COUNTA(QUERY(QUERY(QUERY(A2:D, 
  "select *", 0), 
  "select Col1, Col2, count(Col1) 
   where Col2 is not null 
   group by Col1, Col2 
   order by Col1 desc 
   label count(Col1)''", 0),
  "select Col1,Col2", 0))/2, 1)),
  ARRAYFORMULA(ARRAY_CONSTRAIN(IFERROR(VLOOKUP(QUERY(QUERY(QUERY(A2:D, 
  "select *", 0), 
  "select Col1, Col2, count(Col1) 
   where Col2 is not null 
   group by Col1, Col2 
   order by Col1 desc 
   label count(Col1)''", 0),
  "select Col1", 0)&
  QUERY(QUERY(QUERY(A2:D, 
  "select *", 0), 
  "select Col1, Col2, count(Col1) 
   where Col2 is not null 
   group by Col1, Col2 
   order by Col1 desc 
   label count(Col1)''", 0),
  "select Col2", 0), 
  QUERY(QUERY({A2:A&B2:B, C2:D}, 
  "select *", 0), 
  "select Col1, Col2, Col3 
   where Col3='Y'", 0), 2, 0), 0),
  COUNTA(QUERY(QUERY(QUERY(A2:D, 
  "select *", 0), 
  "select Col1, Col2, count(Col1) 
   where Col2 is not null 
   group by Col1, Col2 
   order by Col1 desc 
   label count(Col1)''", 0),
  "select Col1,Col2", 0))/2, 1))}}