PostgreSQL 9.3: Union two columns in one

Question

I want to show the given records in the following table into the specific format which is shown below in the table.

Creating table: Test_1

CREATE TABLE Test_1
(
 ColumnA varchar,
 ColumnB varchar
);

Insertion of records:

INSERT INTO Test_1 values('A101','B101'),('A102','B102'),
        ('A103','B103'),('A104','B104'),
        ('A105','B105'),('A106','B106'),
        ('A107','B107'),('A108','B108'),
        ('A109','B109'),('A201','B201');

I want to show the result like this:

Expected Result:

ColumnA     ColumnX
---------------------------------------------------------------------------------------------------------------------------------------
A101         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0,"B101" =  1, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A102         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0,"B101" =  0, "B102" = 1, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A103         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0,"B101" =  0, "B102" = 0, "B103" = 1, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A104         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 1, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A105         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 1, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A106         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 1, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A107         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 1, "B108" = 0, "B109" = 0, "B201" = 0
A108         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 1, "B109" = 0, "B201" = 0
A109         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 1, "B201" = 0
A201         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 1
B101         "A101" = 1, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B102         "A101" = 0, "A102"= 1 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B103         "A101" = 0, "A102"= 0 , "A103"= 1, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B104         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 1, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B105         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 1, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B106         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 1, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B107         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 1 , "A108"= 0, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B108         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 1, "A109"= 0, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B109         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 1, "A201"= 0, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
B201         "A101" = 0, "A102"= 0 , "A103"= 0, "A104"= 0, "A105"= 0, "A106" = 0, "A107"= 0 , "A108"= 0, "A109"= 0, "A201"= 1, "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0

Here is the following script does the half work: This script only display the ColumnA in a first Column and ColumnB in the second column.

SELECT columnA, array_agg(ColumnB) AS ColumnX
FROM  (
   SELECT ColumnA, right(ColumnA, -1)::int AS sorta
    , '"' || ColumnB || '" = ' || count(m.ColumnB) AS ColumnB
   FROM        (SELECT DISTINCT ColumnA FROM Test_1) b
   CROSS  JOIN (SELECT DISTINCT ColumnB FROM Test_1) a
   LEFT   JOIN Test_1 m USING (ColumnA, ColumnB)
   GROUP  BY ColumnA, ColumnB
   ORDER  BY sorta, right(ColumnB, -1)::int 
   ) sub
GROUP  BY 1, sorta
ORDER  BY sorta;

Follow-up to this question: http://stackoverflow.com/questions/29247565/postgresql-9-3-display-result-in-specific-format-using-array-agg-function Please provide this to help people gt the picture. Also, there is a fiddle in the answer to work with. — Erwin Brandstetter, Mar 27 '15 at 04:05
And I assume you still want to count duplicates, and just forgot to put some in your test case? — Erwin Brandstetter, Mar 27 '15 at 04:11
@Erwin Brandstetter, Yup! Exactly right. I apologize for that. — MAK, Mar 27 '15 at 04:18

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

Nicely optimized:

WITH t(a, b, ct) AS (
   SELECT columna, columnb, count(*)::int
   FROM   test_1
   GROUP  BY 1, 2
   )
, matrix AS (
   SELECT a.*, b.*, COALESCE(t.ct, 0) AS ct
   FROM        (SELECT DISTINCT ON (a) a, right(a, -1)::int AS sorta FROM t) a
   CROSS  JOIN (SELECT DISTINCT ON (b) b, right(b, -1)::int AS sortb FROM t) b
   LEFT   JOIN t USING (a, b)
   )
(   
SELECT a AS ab, array_agg('"' || b || '" = ' || ct ORDER BY sortb) AS x
FROM   matrix
GROUP  BY 1, sorta
ORDER  BY sorta
)
UNION ALL
(
SELECT b      , array_agg('"' || a || '" = ' || ct ORDER BY sorta)
FROM   matrix
GROUP  BY 1, sortb
ORDER  BY sortb
);

SQL Fiddle.

All parentheses are required.

Sum results of a few queries and then find top 5 in SQL

edited May 23 '17 at 11:50

Community

1
1

answered Mar 27 '15 at 04:51

Erwin Brandstetter

605,456
145
1,078
1,228

This is really awesome. Very well optimized than execpted. Thank you so much. – MAK Mar 27 '15 at 04:54
1

@MAK: Even a bit better now. :) Test with `EXPLAIN ANALYZE`, this one should outperform all previous versions. (Actually, the two final `SELECT` should be even faster with `ORDER BY` in a subquery, but enough of that now ...) – Erwin Brandstetter Mar 27 '15 at 04:57
Yup! This is much better. :) – MAK Mar 27 '15 at 05:00

PostgreSQL 9.3: Union two columns in one

1 Answers1

Linked