POSTGRESQL: Group the values as ARRAY of ARRAYS

Question

Below is my input table of data

Input :

System NameYear Quarter Value 1 Value 2                     
1   2019    Q1  AB  CD                      
1   2019    Q2  EF  GH                      
1   2019    Q3  IJ  KL                      
1   2019    Q4  MN  OP                      
1   2020    Q1  XX  YY                      
1   2020    Q2  ZZ  MM                      
1   2020    Q3  NN  KK                      
1   2020    Q4  TT  QQ

I need the values to be sorted as an ARRAY values of Quarters [Q1,Q2,Q3,Q4] for each system name and under each Quarter it should have the array values of the Years [Y1,Y2]
Value 1 : {{"AB","XX"},{"EF","ZZ"},{"IJ","NN"},{"MN","TT"}}
Value 2 : {{"CD","YY"},{"GH","MM"},{"KL","KK"},{"OP","QQ"}}

The expected output is something like this :

Output :

System Name Value 1                 Value 2             
1   {{"AB","XX"},{"EF","ZZ"},{"IJ","NN"},{"MN","TT"}}               {{"CD","YY"},{"GH","MM"},{"KL","KK"},{"OP","QQ"}}

I have tried using ARRAY_AGG function order by Quarter.

Tim Biegeleisen · Accepted Answer · 2019-10-07 01:55:35Z

1

Use ARRAY_AGG, and perform a two-level aggregation. First, aggregation by system and quarter to generate a series of records containing arrays for each quarter. Then, aggregate a second time by system alone to generate a single array-of-arrays.

WITH cte AS (
    SELECT
        System,
        ARRAY_AGG(Value1 ORDER BY NameYear) AS Val1,
        ARRAY_AGG(Value2 ORDER BY NameYear) AS Val2
    FROM yourTable
    GROUP BY System, Quarter
)

SELECT
    System,
    ARRAY_AGG(Val1) AS Val1,
    ARRAY_AGG(Val2) AS Val2
FROM cte
GROUP BY
    System;

Demo

edited Oct 7, 2019 at 1:55

answered Oct 7, 2019 at 1:49

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GeeBee Over a year ago

Thank you so much for quick response. This is the answer i was looking for!

Collectives™ on Stack Overflow

POSTGRESQL: Group the values as ARRAY of ARRAYS

Input :

Output :

1 Answer 1

Demo

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Input :

Output :

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related