Convert row data to columns in postgresql for display in grafana stats panel

example.sql

-- ========================================================================
-- Convert rows to columns for grafana stats display for enum like columns.
-- ========================================================================

--
-- Create a dummy table.
--
DROP TABLE IF EXISTS xdata CASCADE;
CREATE TABLE IF NOT EXISTS xdata (id  SERIAL PRIMARY KEY, state TEXT NOT NULL);
COMMENT ON TABLE xdata IS 'Example data to show how the convert_rows_to_columns() function works';
INSERT INTO xdata (state)
VALUES
  -- we only care about the status field for this example
  ('passed'), ('passed'), ('passed'), ('passed'), ('passed'), ('passed'), ('aborted'), ('passed'),
  ('unknown'), ('failed'), ('passed'), ('passed'), ('passed'), ('passed'), ('passed'), ('passed'),
  ('passed'), ('passed'), ('passed'), ('passed'), ('passed'), ('passed'), ('failed'), ('passed'),
  ('passed'), ('aborted'), ('passed'), ('failed'), ('failed'), ('passed'), ('passed'), ('passed'),
  ('passed'), ('passed'), ('failed'), ('passed'), ('passed'), ('passed'), ('passed'), ('passed');

--
-- Function to convert rows to columns for grafana stats display
--
DROP FUNCTION IF EXISTS convert_rows_to_columns;
CREATE OR replace FUNCTION convert_rows_to_columns(
       IN tmp text, -- the temporary table name
       IN qtname text, -- the query table name
       IN qcol text -- The query column
       )
  RETURNS void
  LANGUAGE plpgsql
as
$def$
DECLARE row record;
DECLARE str text;
DECLARE q text;
DECLARE s text;
BEGIN
  -- create column headers
  -- collect the distinct row values for crosstab() and store them in str.
  str = '"TOTAL" bigint, ';
  s := 'SELECT DISTINCT ' || qcol || ' AS fld FROM ' || qtname || ' ORDER BY ' || qcol;
  FOR row IN EXECUTE s
  loop
    str :=  str || '"' || row.fld || '" bigint,';
  END loop;
  str := substring(str, 0, LENGTH(str)); -- remove the trailing comma

  -- create and populate the temporary table
  q :=  'CREATE EXTENSION IF NOT EXISTS tablefunc; '
  'DROP TABLE IF EXISTS ' || tmp || '; '
  'CREATE TABLE ' || tmp || ' AS '
  'SELECT * FROM crosstab('
  '$ct$'
  'WITH x1 AS ('
  'SELECT DISTINCT ON (' || qcol || ') ' || qcol || ' as fld, COUNT(*) AS total '
  'FROM ' || qtname ||' GROUP BY fld), '
  'x2 AS (select count(*) as total from ' || qtname || ') '
  'SELECT x2.total as "TOTAL", x1.fld, x1.total::bigint '
  'FROM x1, x2 '
  'GROUP BY x1.fld, x1.total, x2.total '
  '$ct$) AS ct(' || str || ');';
  EXECUTE q;
END;
$def$;

-- Display the row and column views.
\echo '************** row view'
select distinct ON (state) state, COUNT(*) FROM xdata GROUP BY state;

\echo '************** col view'
-- Execute the function to convert the rows to columns in the temporary table
-- and display the columns.
with foo as (select convert_rows_to_columns('temp1', 'xdata', 'state')) select * from temp1;