CODEWITHSUNDEEP
sqlpostgresql
GeeBee
GeeBee

Reputation: 5

POSTGRESQL: Group the values as ARRAY of ARRAYS

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.

Upvotes: 0

Views: 452

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

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;

enter image description here

Demo

Upvotes: 1

Related Questions