user1931485 user1931485 - 11 months ago 96
SQL Question

Redshift SQL: swap and aggregate row to column

Here's a table T1 in Redshift:

enter image description here

I am trying to create a query to represent it in the following manner:

Transformed reporting layout

What is the most optimal way to construct the query. The input table is created dynamically in another SQL query and the resultset of that query will not be small - like 400 to 500 rows / 3 columns.

Greatly appreciate any hints, suggestion, or solution to go about it.


You can use conditional aggregation:

select item,
       sum(case when -period = 'Q1' then counts end) as Q1,
       sum(case when -period = 'Q2' then counts end) as Q2,
       sum(case when -period = 'Q3' then counts end) as Q3
from t
group by item;

If you want the row total, just add sum(counts) after Q3.