I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query.
with checked_adgroup AS (
SELECT
ua.new_adgroup,
ua.account,
ua.campaign,
ua.ad_group,
ua."position",
cp.category,
pt.full_value,
FROM unnest_adgroup ua
LEFT JOIN taxonomy_category cp ON ua."position" = cp."position"
LEFT JOIN taxonomy pt ON ua.short_val = pt.short_value AND cp.category = pt.category AND (pt.lob IS NULL OR pt.lob = ua.lob)
)
SELECT *
from crosstab(
'select
cad.account,
cad.campaign,
cad.ad_group,
cad.category,
cad.full_value
FROM checked_adgroup cad
WHERE cad.all_correct AND cad.category IS NOT NULL
ORDER BY 1,2,3')
AS final_result(
account text, campaign text, ad_group text,
division text, lob text, match_type text )
;
Error message:
ERROR: relation "checked_adgroup" does not exist LINE 7: FROM checked_adgroup cad
Output of checked_adgroup cte looks like below
Desired output of the final statement is:
