How to use Pivot Tables in Bold BI Using PostgreSQL

In the Bold BI application, use the required tables to create a Pivot table in the PostgreSQL data source using the Crosstab function. In this example, the following list of tables are used.

1. Products
   

   

2. Customers
   

   

3. Product_customers
   

   

4. V_product_customers
   

   

Follow these steps to create and use Pivot tables in Bold BI.

1. Enable the Crosstab function in the PostgreSQL data source using the following command.

   CREATE EXTENSION IF NOT EXISTS tablefunc;
   

   

2. Connect to a PostgreSQL data connection in Bold BI.

3. Switch to the code view mode of a data source.
   

   

4. Write a query using the Crosstab function in the query designer page to utilize the necessary tables from a data source, then click the highlighted Run button to execute the query.
   

   SELECT * FROM crosstab( 
   $$SELECT customers_name, product_name, SUM(cost) AS cost
     FROM v_product_customers
      GROUP BY customers_name, product_name
      ORDER BY customers_name$$,
   $$SELECT 'Tweetholdar' UNION ALL
     SELECT 'Promuton' UNION ALL
     SELECT 'Transniollor' UNION ALL
     SELECT 'Cleanputon' UNION ALL
     SELECT 'Tabwoofphone' UNION ALL
     SELECT 'Supceivra' UNION ALL
     SELECT 'Supputommar' UNION ALL
     SELECT 'Mictellar' UNION ALL
     SELECT 'Armlififiator' UNION ALL
     SELECT 'Monoculimry'$$
   )
   AS ct(customers_name VARCHAR, Tweetholdar NUMERIC,Promuton NUMERIC,Transniollor NUMERIC,Cleanputon NUMERIC,Tabwoofphone NUMERIC,Supceivra NUMERIC,Supputommar NUMERIC,Mictellar NUMERIC,Armlififiator NUMERIC,Monoculimry NUMERIC);
   
   

5. Now, click Update to view the query result of the pivot table.