在 LibreOffice Calc 中，如何将包含调查回复的工作表转换为二维网格？

Question

例如，如果我有以下数据：

Name    Question                            Answer
Alice   What is your favorite color?        blue
Alice   What is your favorite video game?   Minecraft
Alice   What is your favorite number?       42
Bob     What is your favorite color?        red
Bob     What is your favorite video game?   Team Fortress 2
Bob     What is your favorite number?       7
Charlie What is your favorite color?        green
Charlie What is your favorite video game?   Candy Crush
Charlie What is your favorite number?       -1

我希望它的格式如下：

Name    What is your favorite color?    What is your favorite video game?   What is your favorite number?
Alice   blue                            Minecraft                           42
Bob     red                             Team Fortress 2                     7
Charlie green                           Candy Crush                         -1

有没有办法在 LibreOffice Calc 中做到这一点？（或者在 SQL Server 中；那是我从那里获取数据的地方。）

Answer 1

笔记： 在最初的问题中，发帖者提到使用 Excel 作为一种可能的解决方案。他后来修改了他的问题，澄清他只想要 LibreOffice 或 SQL 解决方案。但我将保留这个答案，以防 Excel 用户将来偶然遇到这个问题

您可以在 Excel 中使用Power Query（在 Excel 2010 及更高版本中可用）。

• Question以柱为轴
• 选择Answers列Values​
• 在Advanced Options选择下Don't aggregate

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Question", type text}, {"Answer", type any}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Question]), "Question", "Answer")
in
    #"Pivoted Column"

源数据

结果