Converting rows to columns using crosstab in PostgreSQL not working (relation "table" does not exist)

Question

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:

Answer 1

The CTE is not visible inside the crosstab() function. You have to pass the complete query string including the CTE.

Possible alternative: create a temporary table instead of the CTE.

However, there are more problems in your query. all_correct seems undefined. Etc.

Basic instructions:

• PostgreSQL Crosstab Query