Question 89745 in PostgreSQL Question posted in
The official documentation can be found here.

How to create a pivot table in PostgreSQL

I am looking to essentially create a pivot view using PostgreSQL, such that the table below:

Column A Column B
Happy Sad
Sad Happy
Happy Sad

becomes

Count Column A Column B
Happy 2 1
Sad 1 2

I’ve been able to use case/when operators far enough such that I can see the counts under independent columns,

SELECT
    COUNT(CASE WHEN column1 = 'Happy' THEN 1 END) AS column1_Happy_count,
    COUNT(CASE WHEN column1 = 'Sad' THEN 1 END) AS column1_Sad_count,
    COUNT(CASE WHEN column2 = 'Happy' THEN 1 END) AS column2_Happy_count,
    COUNT(CASE WHEN column2 = 'Sad' THEN 1 END) AS column2_Sad_count,
    COUNT(CASE WHEN column3 = 'Happy' THEN 1 END) AS column3_Happy_count,
    COUNT(CASE WHEN column3 = 'Sad' THEN 1 END) AS column3_Sad_count
FROM your_table;

but am missing the step to essentially each the pair of columns vertically.

I’m unable to use extensions such as tablefunc and crosstab.

Tags:

2

Answers


  1. 0

    Try this:

    CREATE TABLE my_table (
  column_a varchar(10),
  column_b varchar(10)
);

INSERT INTO my_table (column_a, column_b)
VALUES ('Happy', 'Sad'),
       ('Sad', 'Happy'),
       ('Happy', 'Sad'),
       ('Good', 'Bad');

WITH DataSource (col, val) AS
(
     SELECT 'a', column_a
     FROM my_table
     UNION ALL
     SELECT 'b', column_b
     FROM my_table
)
SELECT uniq.val AS "Count"
      ,MAX(case when counts.col = 'a' then counts end) AS "Column A"
      ,MAX(case when counts.col = 'b' then counts end) AS "Column B"
FROM
(
     SELECT DISTINCT val
     FROM DataSource
) uniq
INNER JOIN
(
   SELECT col
         ,val
         ,COUNT(*) counts
   FROM DataSource
   GROUP BY col
          ,val
) counts
  ON uniq.val = counts.val
GROUP BY uniq.val

    will give you this:

    enter image description here

    Login or Signup to reply.
  2. 0

    You may aggregate for ColumnA, aggregate for ColumnB then do a full join as the following:

    select coalesce(A.ColumnA ,B.ColumnB) as "Count",
       A.cnt as "Column A",
       B.cnt as "Column B"
from
(
  select ColumnA, count(*) cnt
  from tbl_name
  group by ColumnA
) A
full join 
(
  select ColumnB, count(*) cnt
  from tbl_name
  group by ColumnB
) B
on A.ColumnA = B.ColumnB

    If the distinct values in ColumnA are the same as the distinct values of ColumnB then you can use join instead of the full join.

    See demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search