Here's a table T1 in Redshift:
I am trying to create a query to represent it in the following manner:
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