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.
Products
Customers
Product_customers
V_product_customers
Follow these steps to create and use Pivot tables in Bold BI.
Enable the Crosstab function using the following command in the PostgreSQL data source.
CREATE EXTENSION IF NOT EXISTS tablefunc;
Connect to PostgreSQL data connection in Bold BI.
Switch to the code view mode of a data source.
Write a query using the Crosstab function in the query designer page to use the required tables of a data source and click the following 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);
Now, click Update
to view the query result of the pivot table.