'convert row values to single column as array (or to multiple columns)

I have 3 tables e.g. emptable, tax (1:m with emptable using empid), deductions (1:m with emptable using empid)

========== emptbl ==========
empid, totaltax, totaldeductions...
001, 100, 50

========== tax ========== taxes can be 1 line item per empid or many line items
empid, date, tax_line_item, tax, tax_type...
001, 12-15-2021, 1, 25, A
001, 12-15-2021, 2, 30, B
001, 12-15-2021, 3, 25, C

========== deductions ========== deductions can be 1 line item per empid or many line items
empid, date, ded_line_item, deduction, deduction_type...
001, 12-15-2021, 1, 12.5, W
001, 12-15-2021, 2, 12.5, 401
001, 12-15-2021, 3, 20, I
001, 12-15-2021, 4, 5, B

Desired result

========== RESULT ==========
empid, totaltax, totaldeductions, taxes (as array), tax_type , deductions (as array), deduction_type
001,   100,      50,              25,       A,        12.5,        W
                                  30        B,        12.5,        401
                                  25        C,        20,          I
                                                      5,           B

If I use the emptable and tax, I would get 1 row and the taxes column as array, which is what I want to achieve but when I join the 3 tables and use array_agg to get the RESULT#2, i get 12 rows (1x3x4) instead of just 4 rows total.

Is there a way a could get the desired result. I appreciate any leads on how to make it happen.

sqlgoogle-bigquery


Solution 1:[1]

select a.empid,a.totaltax, a.totaldeductions,string_agg(distinct b.tax::text,',') as taxes , string_agg(distinct c.ded::text,',') as deductions 
from emptbl a, tax b, deductions c
where a.empid=b.empid and b.empid=c.empid
group by a.empid,a.totaltax, a.totaldeductions

i'm not sure about distinct,but i think this should work.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 mikasa

Related Questions