admin管理员组文章数量:1417070
I want to normalize following table in Google Sheet:
| Process | Tool A | Tool B |
|---|---|---|
| Process 1 | Tool 1 | Tool 2 |
| Process 2 | Tool 1 | |
| Process 3 | Tool 2 | Tool 3 |
I want to normalize following table in Google Sheet:
| Process | Tool A | Tool B |
|---|---|---|
| Process 1 | Tool 1 | Tool 2 |
| Process 2 | Tool 1 | |
| Process 3 | Tool 2 | Tool 3 |
into the following 1NF table:
| Process | Tool A | Tool B |
|---|---|---|
| Process 1 | Tool 1 | |
| Process 1 | Tool 2 | |
| Process 2 | Tool 1 | |
| Process 3 | Tool 2 | |
| Process 3 | Tool 3 |
There are various options (Google Sheets QUERY() function, etc.).
Share Improve this question edited Feb 4 at 12:19 philipxy 15.2k6 gold badges43 silver badges97 bronze badges asked Feb 2 at 20:27 SaxSax 112 bronze badges3 Answers
Reset to default 1Use wraprows(), like this:
=let(
get_, lambda(a, filter(hstack(A2:A, a), len(a))),
wraprows(tocol(hstack(get_(B2:B), get_(C2:C)), 3), 2)
)
See let(), lambda(), hstack(), filter(), wraprows(), and tocol().
Try this Alternative approach
=QUERY(ARRAYFORMULA(SPLIT(FILTER(FLATTEN(A2:A&"|"&B2:C), FLATTEN(B2:C)<>""), "|")),"SELECT Col1, Col2 LABEL Col1 'Process', Col2 'Tool A'",0)
Expected Output
| Process | Tool A |
|---|---|
| Process 1 | Tool 1 |
| Process 1 | Tool 2 |
| Process 2 | Tool 1 |
| Process 3 | Tool 2 |
| Process 3 | Tool 3 |
References
QUERY()
FLATTEN()
SPLIT()
You may try:
=reduce(A1:B1,B2:C,lambda(a,c,vstack(a,if(c="",tocol(,1),{index(A:A,row(c)),c}))))
本文标签： sqlQuery for normalized Google Sheet tableStack Overflow
版权声明：本文标题：sql - Query for normalized Google Sheet table - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1745257854a2650207.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论