SQL：在Chartio中动态地移动列作为行

Question

我很想将您的帮助转换为连接此表的列。我试图让项目名称（第1列）成为它们自己的列标题，并输入作为列值的MUV值。

我已阅读了关于此主题的20多个主题，但他们正在使用不同的SQL程序包和我无法使用的代码。

理想的解决办法修改这个查询，以创建所需的查询输出：

当前查询：

SELECT 
    cust_gae_account.project_name as "Project Name", 
    cust_be_project_usage.unique_visitor_count as "MUVs", 
    cust_be_project_usage.billing_period_start as "Month" 
FROM 
    cust_be_project_usage 
INNER JOIN 
    cust_gae_account 
    ON cust_gae_account.account_id = cust_be_project_usage.project_id 
WHERE 
    cust_be_project_usage.admin_account_id = {ACCOUNT_ID} 

当前查询输出：

Project Name  |  MUVs  |  Month 
----------------------------------------------------- 
ProjectAAAAAZ |  68000  | Jun 01, 2016 
DynamicName  |  3200  | Jun 01, 2016 
ProjectAAAAAZ |  21000  | May 01, 2016 
DynamicName  |  4500  | May 01, 2016 
CustomProject |  117000  | Jun 01, 2016 
CustomProject |  118400  | May 01, 2016 

所需的查询输出：

Project Name  | CustomProject | ProjectAAAAAZ | DynamicName 
------------------------------------------------------------------- 
Jun 01, 2016  |  117000  |  68000  |  3200 
May 01, 2016  |  118400  |  21000  |  4500 

注：

• 我们通过Chartio使用PostgreSQL，我们只能使用基本SQL
• 我们不能使用自定义功能，遗憾的是
• 名称和项目数量是动态的，从1到20不等
• MUV总是数字，没有重复

Answer 1

您可以使用交叉表https://www.postgresql.org/docs/9.1/static/tablefunc.html，但它仅适用于列的静态列表。所以你需要一个动态查询。您需要定义该查询并执行它。

EXECUTE ('SELECT * FROM crosstab(
    \'SELECT 
     cust_be_project_usage.billing_period_start as "Month", 
     cust_gae_account.project_name as "Project Name", 
     cust_be_project_usage.unique_visitor_count as "MUVs" 
    FROM 
     cust_be_project_usage 
    INNER JOIN 
     cust_gae_account 
     ON cust_gae_account.account_id = cust_be_project_usage.project_id 
    WHERE 
     cust_be_project_usage.admin_account_id = {ACCOUNT_ID}\', 
    \'SELECT 
     cust_gae_account.project_name as "Project Name" 
    FROM 
     cust_be_project_usage 
    INNER JOIN 
     cust_gae_account 
     ON cust_gae_account.account_id = cust_be_project_usage.project_id 
    WHERE 
     cust_be_project_usage.admin_account_id = {ACCOUNT_ID}\') 
AS ("Project Name" date, ' || 
    (SELECT 
     string_agg(cust_gae_account.project_name, ' int,') 
    FROM 
     cust_be_project_usage 
    INNER JOIN 
     cust_gae_account 
     ON cust_gae_account.account_id = cust_be_project_usage.project_id 
    WHERE 
     cust_be_project_usage.admin_account_id = {ACCOUNT_ID}) || ' int' 
|| ')') 

虽然我无法测试它。它应该工作，但我的怀疑是，它可能没有，在这种情况下：

1. 串联到由查询所形成的标量（可能需要使用一个变量为一个）
2. 我可能已经混了一列某处
3. 不知道如何传递{ACCOUNT_ID}参数，所以你可能需要太