大查询-将特定字段转置为列 [英] Big Query - Transpose Specific fields into Columns

问题描述

我们在Big Query中有一个表格，如下所示.

We have a table in Big Query like below.

输入表:

 Name | Question  | Answer
 -----+-----------+-------
 Bob  | Interest  | a     
 Sue  | Interest  | a
 Sue  | Interest  | b
 Joe  | Interest  | b
 Joe  | Gender    | Male
 Bob  | Gender    | Female
 Sue  | DOB       | 2020-10-17

我们希望将上表转换为以下格式，使其对BI/可视化友好.

We want to convert the above table to the below format to make it BI/Visualisation friendly.

目标/必需表:

 +----------------------------------------+
 | Name | a | b | c | Gender | DOB        |
 +----------------------------------------+
 | Bob  | 1 | 0 | 0 | Female | 2020-10-17 |
 | Sue  | 1 | 1 | 0 |   -    |     -      |
 | Joe  | 0 | 1 | 0 |  Male  |     -      |
 +----------------------------------------+

推荐答案

以下内容适用于 BigQuery标准SQL ，并且不依赖知道特定的问题并且对任何问题都足够通用问题和答案的值

Below is for BigQuery Standard SQL and does not depend on knowing specific questions and generic enough for any values of questions and answers

EXECUTE IMMEDIATE (
  SELECT """
    SELECT name, """ || STRING_AGG("""MAX(IF(answer = '""" || value || """', 1, 0)) AS """ || value, ', ')   
FROM (
  SELECT DISTINCT answer value FROM `project.dataset.table`
  WHERE question = 'Interest' ORDER BY value
)) || (
  SELECT ", " || STRING_AGG("""MAX(IF(question = '""" || value || """', answer, '-')) AS """ || value, ', ')   
FROM (
    SELECT DISTINCT question value FROM `project.dataset.table`
    WHERE question != 'Interest' ORDER BY value
)) || """  
  FROM `project.dataset.table` 
  GROUP BY name
  """;    

这篇关于大查询-将特定字段转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！