PostgreSQL 9.3: Display result in specific format using array_agg function

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        "B101" =  1, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A102        "B101" =  0, "B102" = 1, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A103        "B101" =  0, "B102" = 0, "B103" = 1, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A104        "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 1, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A105        "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 1, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A106        "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 1, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 0
A107        "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 1, "B108" = 0, "B109" = 0, "B201" = 0
A108        "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 1, "B109" = 0, "B201" = 0
A109        "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 1, "B201" = 0
A201        "B101" =  0, "B102" = 0, "B103" = 0, "B104" = 0, "B105" = 0, "B106" = 0, "B107" = 0, "B108" = 0, "B109" = 0, "B201" = 1

For which I am using the following script which does my half job:

SELECT columnA, array_agg(ColumnB) AS ColumnX
FROM  (
   SELECT ColumnA, right(ColumnA, -1)::int AS sortb
    , CASE WHEN m.ColumnB IS NULL THEN 0 ELSE 1 END 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)
   ORDER  BY sortb, right(ColumnB, -1)::int 
   ) sub
GROUP  BY 1, sortb
ORDER  BY sortb;

The above script gives me the following result:

Getting result:

ColumnA          ColumnX
---------------------------------------
A101        {1,0,0,0,0,0,0,0,0,0}
A102        {0,1,0,0,0,0,0,0,0,0}
A103        {0,0,1,0,0,0,0,0,0,0}
A104        {0,0,0,1,0,0,0,0,0,0}
A105        {0,0,0,0,1,0,0,0,0,0}
A106        {0,0,0,0,0,1,0,0,0,0}
A107        {0,0,0,0,0,0,1,0,0,0}
A108        {0,0,0,0,0,0,0,1,0,0}
A109        {0,0,0,0,0,0,0,0,1,0}
A201        {0,0,0,0,0,0,0,0,0,1}

Question: How to add columnB values infront of values of getting in ColumnX?

Does this [output](http://pastie.org/10051659) is acceptable ?? — Vivek S., Mar 25 '15 at 05:19
So test [this](http://pastie.org/10051672),If worked then I'll Post it as Answer :) — Vivek S., Mar 25 '15 at 05:23
@unique_id, Almost done. Only the change is, if I insert same record for example `('A101','B101')` two times then it has to show me `B101= 2`. In your case it is show like `B101=1 , B101=1`. — MAK, Mar 25 '15 at 05:29
This is a follow-up to this previous question: http://stackoverflow.com/questions/28337765/postgresql-9-3-dynamic-pivot-table/29011847 — Erwin Brandstetter, Mar 25 '15 at 05:38

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

It's just a simple modification.
Also added the actual count as requested in your comment.

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;

SQL Fiddle.

One array

As per comment:

SELECT ARRAY[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 right(ColumnB, -1)::int, sorta
   ) sub
GROUP  BY columnA, sorta
ORDER  BY sorta;

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 25 '15 at 05:29

Erwin Brandstetter

605,456
145
1,078
1,228

Almost done. Only the change is, if I insert same record for example `('A101','B101')` two times then it has to showing me `B101= 2`. In your case it is show like `B101=1 , B101=1`. – MAK Mar 25 '15 at 05:31
Only need to edit in your answer is: add `m.ColumnB` into the `GROUP BY`. Well! that is understandable. – MAK Mar 25 '15 at 05:44
@MAK: No, this is better. Use `ColumnB` in the expression without table qualification (which was wrong). – Erwin Brandstetter Mar 25 '15 at 05:48
Can I able to union both columns `ColumnA` and `ColumnB` and display in the `ColumnX`? – MAK Mar 26 '15 at 07:06
@MAK: I added a query. Also changed the column alias "sortb" to "sorta". – Erwin Brandstetter Mar 26 '15 at 07:22
I want to union `ColumnA` and `ColumnB` values like :`A101=0, A102=0, A103=0.........B101=1,B102=0,B1-3=0`. – MAK Mar 26 '15 at 07:31
@MAK: I suggest you ask a new question. – Erwin Brandstetter Mar 26 '15 at 07:44
Here is the [new question](http://stackoverflow.com/questions/29293033/postgresql-9-3-union-two-columns-in-one). – MAK Mar 27 '15 at 03:59

score 1 · Answer 2 · answered Mar 25 '15 at 05:36

1

SELECT columnA, array_agg(ColumnB) AS ColumnX
FROM  (
  SELECT ColumnA, right(ColumnA, -1)::int AS sortb
    , CASE WHEN m.ColumnB IS NULL THEN NULL ELSE m.ColumnB||'='||( count(m.ColumnB)) END 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,m.ColumnB,ColumnB
   ORDER  BY sortb, right(ColumnB, -1)::int 

   ) sub
GROUP  BY 1, sortb
ORDER  BY sortb;

answered Mar 25 '15 at 05:36

Vivek S.

19,945
7
68
85

Thank you so much. But according to FIFO I suppose to accept Erwin's answer. Sorry for that :( – MAK Mar 25 '15 at 05:38
1

@MAK It Doesn't Matter :) – Vivek S. Mar 25 '15 at 05:40
Thanks, but +1 for sure. – MAK Mar 25 '15 at 05:40

PostgreSQL 9.3: Display result in specific format using array_agg function

2 Answers2

One array

Linked